Check date field for null

[magster06]

Hello All,

I am trying to create a query that checks for a couple of criteria. The first one works fine, but the second one I am having a bit of trouble with.

Here is the code:

SELECT LightDuty.FDID, LightDuty.[First Name], LightDuty.[Last Name], LightDuty.Unit, LightDuty.[Permanent Assignment], LightDuty.[Drs Excuse Exp], LightDuty.[Email Address], LightDuty.[Email Sent Date]
FROM LightDuty
WHERE (((LightDuty.[Email Sent Date])=IsNull([Email Sent Date])) AND ((DateDiff('d',Date(),[Drs Excuse Exp]))=14));

What I am after is for the code to check the [Email Sent Date] field to check if it is blank.

 

[John Big Booty]

Try;

SELECT LightDuty.FDID, LightDuty.[First Name], LightDuty.[Last Name], LightDuty.Unit, LightDuty.[Permanent Assignment], LightDuty.[Drs Excuse Exp], LightDuty.[Email Address], LightDuty.[Email Sent Date]
FROM LightDuty
WHERE (((LightDuty.[Email Sent Date])Is Null) AND ((DateDiff('d',Date(),[Drs Excuse Exp]))=14));

 

[magster06]

John Big Booty,

That did the trick!

I knew that the function of IsNull was not the correct thing to use; I even used IsNull by itself.

I think I tried every combo, but I never tried splitting the IsNull to Is Null, arrgh!

Anyway, thanks for the help!!

Can anyone recommend a really good access vba book?

 

[Brianwarnock]

IsNull is used in VBA IsNull(fieldname)

Brian

 

[gemma-the-husky]

to be honest, rather than testing for null, i would probably do

if nz(somedate,0)=0 and test for zero, on the basis that no normal record would have a date of 1899.