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

Good day everyone,


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;


Thanks for looking!


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


Good day everyone,


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;


Thanks for looking!


Guy