CODEWITHSUNDEEP
sqlms-access-2007
Asynchronous
Asynchronous

Reputation: 3977

How to work around or to remedy Error: Invalid Use of Null?

In this piece of MS Access Code I am trying to get the MAX Date of a Customer signup. First I have to convert the date so it can work with an aggregate function. Unfortunately there are areas in the date column that are null.

I am getting the error Invalid use of null due to some records missing dates.

How can I remedy that, is there a work around?

Here is the code:

SELECT CUSTOMER.FIRST_NAME, 
    MAX(DateSerial(CInt(Left([CUSTOMER.SIGNUP_DATE],4)),CInt(Mid([CUSTOMER.SIGNUP_DATE],5,2)),CInt(Right([CUSTOMER.SIGNUP_DATE],2)))) AS SIGN_DATE, 
    (DateSerial(CInt(Left([CUSTOMER.LEAVE_DATE],4)),CInt(Mid([CUSTOMER.LEAVE_DATE],5,2)),CInt(Right([CUSTOMER.LEAVE_DATE],2)))) AS LEV_DATE
FROM CUSTOMER
WHERE ((DateSerial(CInt(Left([CUSTOMER.SIGNUP_DATE],4)),CInt(Mid([CUSTOMER.SIGNUP_DATE],5,2)),CInt(Right([CUSTOMER.SIGNUP_DATE],2)))) <=Date())
    AND ((DateSerial(CInt(Left([CUSTOMER.LEAVE_DATE],4)),CInt(Mid([CUSTOMER.LEAVE_DATE],5,2)),CInt(Right([CUSTOMER.LEAVE_DATE],2)))) =#012/31/2012#)
GROUP BY
    CUSTOMER.FIRST_NAME,
    CUSTOMER.SIGNUP_DATE,
    CUSTOMER.LEAVE_DATE;

Upvotes: 1

Views: 2304

Answers (1)

HansUp
HansUp

Reputation: 97101

Your SIGNUP_DATE field contains dates as strings in this format: "20120131". So you're using Left(), Mid(), and Right() functions to split out the year, month, and day substrings, converting them to numbers with CInt() and finally feeding those numbers to DateSerial().

If only your date string included suitable delimiters between the 3 parts, you could simply feed the string to CDate(), for example CDate("2012-01-31"). So I propose you add delimiters to the string in a query and feed that to CDate().

Here is my simplified CUSTOMERS table:

id SIGNUP_DATE
1  20120130
2 
3  20120131

This query transforms the non-Null SIGNUP_DATE values to Date/Time values and discards rows where SIGNUP_DATE is Null (the row with id=2 is excluded).

SELECT
    id,
    CDate(Left(SIGNUP_DATE,4) & "-"
        & Mid(SIGNUP_DATE,5,2) & "-" &
        Right(SIGNUP_DATE,2))
        AS SIGN_DATE
FROM CUSTOMER
WHERE SIGNUP_DATE Is Not Null;

Then getting the Max value is simple.

SELECT Max(q.SIGN_DATE) AS MaxOfSIGN_DATE
FROM (
        SELECT
            id,
            CDate(Left(SIGNUP_DATE,4) & "-"
                & Mid(SIGNUP_DATE,5,2) & "-" &
                Right(SIGNUP_DATE,2))
                AS SIGN_DATE
        FROM CUSTOMER
        WHERE SIGNUP_DATE Is Not Null
    ) AS q;

Edit: With no sample data to test against, I'll just wing it and suggest you try this query:

SELECT
    q.FIRST_NAME,
    #2012/12/31# AS LEV_DATE,
    Max(q.SIGN_DATE) AS MaxOfSIGN_DATE
FROM (
        SELECT
            FIRST_NAME,
            CDate(Left(SIGNUP_DATE,4) & "-"
                & Mid(SIGNUP_DATE,5,2) & "-" &
                Right(SIGNUP_DATE,2))
                AS SIGN_DATE
        FROM CUSTOMER
        WHERE
            SIGNUP_DATE Is Not Null
            AND LEAVE_DATE = "20121231"
    ) AS q
GROUP BY
    1,
    2;

Upvotes: 1

Related Questions