Query Query

[Paul Richards]

I can’t understand why my query is returning nothing – I have set 2
criteria in the query design:

DATE DEPOSIT RECEIVED <> IsNull i.e. presence of date indicates deposit
received

DATE DEPOSIT LETTER SENT IsNull i.e. lack of date indicates letter
not sent

Both fields are DATE/TIME

My test data explicitly simulates these conditions but the query is
returning nothing.

There are no other criteria set for the remaining fields in the query.

It’s probably something very obvious but, at the moment, I can’t see
what it is – unless it’s something to do with the Null test on a
DATE/TIME field.

Please advise.

 

[Duncan Bachen]

I can’t understand why my query is returning nothing – I have set 2
criteria in the query design:

DATE DEPOSIT RECEIVED <> IsNull i.e. presence of date indicates
deposit received

DATE DEPOSIT LETTER SENT IsNull i.e. lack of date indicates
letter not sent

Both fields are DATE/TIME

My test data explicitly simulates these conditions but the query is
returning nothing.

There are no other criteria set for the remaining fields in the query.

It’s probably something very obvious but, at the moment, I can’t see
what it is – unless it’s something to do with the Null test on a
DATE/TIME field.

Please advise.

Yup, it's definately the why you are testing for a Null value.
Is Null and IsNull(val) are two different things. One is used as
criteria to check against a known value, and the other is a function to
which you supply the value. When you check against a non-null value, you
would use Is Not Null

Your SQL should look like this:
SELECT DateDepositReceived, DateDepositLetterSent
FROM tblYourTable
WHERE (DateDepositReceived Is Not Null) AND (DateDepositLetterSent Is Null);

 

[Paul Richards]

Duncan: thank you for pointing out the differences between the Null
test. That solved the problem.

 

[Duncan Bachen]

Duncan: thank you for pointing out the differences between the Null
test. That solved the problem.

NP. Glad it worked for you. Nulls can be tricky to get your head around.
Once you figure it out though, it'll stick with ya.