Solved: SQL SELECT...Invalid Use of Null

[stanl]

I inherited a table where a text field contains dates, and some rows have no entry in that field. I need to have the dates converted - say 9/11/07 --> 20071109

and if I issue SQL like

SELECT f1,f2,format(cDate(f3),"yyyymmdd") WHERE f3 is not null;

I get desired output, but If I add AND format(cDate(f3),"yyyymmdd")<'20071109' I get the error (invalid use of null). I want to avoid a 2 step process and wondered if there was a way to obtain the filtered results w/out the error.

Stan

[Tommy]

Hey Stan,

This is a shot in the dark with no place to test so have you tried this?

SELECT f1, f2, Format(cDate(f3),"yyyymmdd") WHERE f3 is not null AND IIf(IsNull(f3), "9/11/09", f3) < '20071109' ;

[stanl]

Nah; I tried both isnull and iserror with various combinations, the query returns all valid rows and ignores the <'20071016' :banghead:

[Tommy]

I have forgotten what the qualifier is for dates in Access. But the idea is to select dates before 9/11/07 and after 1/1/1912 in the format in access so it does all the work and we don't have to break our heads on it. :)
WHERE f3 < '9/11/07' and f3 > '1/1/12';

[jtrowbridge]

I recreated the problem with a table that I knew had some NULLS in it and I didnt have a problem. I know thats not what you want to hear but maybe you should try converting the date into an integer?

CONVERT(INT, format(cDate(f3),"yyyymmdd")

I'm using MS SQL Server though, it looks like youre using some other SQL platfrom based on the FORMAT function. You'd maybe need to use CAST or something.

Sorry I'm not much help.

[stanl]

I'm using MS SQL Server though, it looks like youre using some other SQL platfrom based on the FORMAT function.




Yeah, probably why they called this an Access Forum. Actually I tried something similar to what your suggested


SELECT RA.cRMA, RA.cDISHRA,iif(isnull(cLng( Format(cDate(RA.cRollover),"yyyymmdd"))),0,cLng( Format(cDate(RA.cRollover),"yyyymmdd"))) as Deadline
FROM RA ;



And the errors still show as #Error and thus I cannot filter. This is easy enough to recreate... simply create a table with a text field (10 char) and fill it with a few dates and leave a few nulls. I think its a bug with respect to straight SQL, maybe have to write a UDF to act as a stored proc to return a proper value... problem is the Access table is standalone with no embedded code, so I'm running this via ADO...:banghead:

[OBP]

stanl, as this is a VBA forum as well why not use VBA as it is far more versatile?
Either to loop through a recordset and convert it or to have a function to call in your sql to convert it (like you showed me how last year).

[stanl]

stanl, as this is a VBA forum as well why not use VBA as it is far more versatile?
Either to loop through a recordset and convert it or to have a function to call in your sql to convert it (like you showed me how last year).

that would be Plan B... but I'm just so jacked about distinguishing between a valid and invalid use of a null {not withstanding the current candidates for President}.

The issue is related to querying a large inventory database for tickler dates related to RMA's - the RMA is generated from a website submission and returns a status, but the item must be returned in 15 days with the RMA return email. The cRollover date is usually set 2-3 days prior to the RMA expiration. I am only beginning to learn the hundreds of seemingly valid reasons why cRollover can be null in the first place:dunno

[Tommy]

FWIW the below code gives an invalid use of Null.

f3 = Null
MsgBox CDate(f3)


This did not


f3 = Null
MsgBox CDate(IIf(IsNull(f3), 0, f3))


EDIT Didn't paste it all

[stanl]

Yes, but the issue is not with representing a null text/date but with applying the filter < yyyymmdd while eliminating the nulls in the first place.

[akn112]

just my quick thoughts

couldn't u just write it like:

format(nz(cDate(f3),#9000/01/01#),"yyyymmdd") < #2007/11/09# or

format(cDate(nz(f3,90000101)),"yyyymmdd") < #2007/11/09#

[Tommy]

Stan I got Access to quit crying with this hope it helps

SELECT Table1.somedate
FROM Table1
WHERE (([Table1]![somedate] Is Not Null And Format(CStr(CDate(IIf([Table1]![somedate] Is Not Null,[Table1]![somedate],0))),"yyyy/mm/dd") > Format(CStr(#4/21/2007#),"yyyy/mm/dd")));

:dunno

[DarkSprout]

SELECT f1,f2,format(cDate(f3),"yyyymmdd") WHERE f3 is not null;
Stan

All you need do is wrap the input field with Nz(InputField,"")

So...


SELECT f1,f2,format(cDate(Nz(f3,"")),"yyyymmdd") WHERE Nz(f3,"") <> "";
.

Or...


SELECT f1,f2,format(cDate(Nz(f3, #01/01/1900#)),"yyyymmdd") WHERE Nz(f3, #01/01/1900#) > #01/01/1900#;
.

You'll never see a null again

[stanl]

Yes, Yes... nz() was the ticket so something like


SELECT RA.cRollover
FROM RA WHERE cDate(nz([RA].[cRollover],"12/21/2012")) < cDate(myVar);


where myVar is established from a dropdown from the current date +/- 5 days.

Thanks to everyone who contributed. P.S. of course y'all know that 12/21/2012 is the official end of the world according to the Mayan Calendar. Stan