Select query - result is "Invalid use of Null" 1

[jancheta]

Hi. I'm trying to write a select a query but get an error message - "Invalid Use of Null"

Table: tblBooks
FIELD NAME TYPE
BOOK_ID TEXT
CHK_OUT_DT DATE
RETURN_DT TEXT

Sample data
--------------------------------------------------
|BOOK_ID | CHK_OUT_DT | RETURN_DT
--------------------------------------------------
US432344 | 12/13/2003 | 12/15/2003 B.KERNIGHAN
PH322322 | 12/01/2003 |
US221277 | 12/10/2003 | 12/17/2003 M.RITCHIE
.
.
.

Question: Which BOOK_ID exceeded the limit of 5 days?
(I'm trying to just write one select query that will do this but I get the error)
SELECT BOOK_ID FROM tblBooks
WHERE ((RETURN_DT) Is Not Null) And
((CDATE(LEFT(RETURN_DT,10)) - CHK_OUT_DT) > 5);

Thanks in advance!

Jason

 

[nicsin]

Hi Jason,

I think that CHK_OUT_DT is null somewhere. try this instead:

SELECT BOOK_ID FROM tblBooks
WHERE RETURN_DT Is Not Null And CHK_OUT_DT Is Not Null And CDATE(LEFT(RETURN_DT,10)) - CHK_OUT_DT > 5;

 

[jancheta]

Hi nicsin,

Thanks for the reply. I tried it but still got the same error. Problem is that for some of the records, the RETURN_DT is actually null but for some reason, access won't process it.

Jason

 

[nicsin]

Jason,

what is the datatype of RETURN_DT,10 and CHK_OUT_DT?

Try this:

SELECT BOOK_ID FROM tblBooks
WHERE RETURN_DT Is Not Null And CHK_OUT_DT Is Not Null And CDATE(LEFT(iif(RETURN_DT is null,"0",RETURN_DT),10)) - iif(CHK_OUT_DT is null, 0, CHK_OUT_DT) > 5;

 

[jancheta]

Hi nicsin,

Thanks!!! It worked. Whoa, now I know that each field should be thoroughly validated.

Crafty idea on the zeroes. Thanks for your help.

Regards,

Jason

 

[nicsin]

You're welcome Jason. The zeros are actually 'dummy' values since they are never used.

Take care