Query Query

  • Thread starter Thread starter Paul Richards
  • Start date Start date
P

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.
 
Paul said:
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.
Click to expand...

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);
 
Duncan: thank you for pointing out the differences between the Null
test. That solved the problem.
 
Paul said:
Duncan: thank you for pointing out the differences between the Null
test. That solved the problem.
Click to expand...

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.
 
Back
Top