Query Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Richards - 28 Mar 2006 16:13 GMT

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.

Reply to this Message

Duncan Bachen - 28 Mar 2006 16:30 GMT

> I can’t understand why my query is returning nothing – I have set 2
> criteria in the query design:
[quoted text clipped - 17 lines]
>
> 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);

Signature

-D
Duncan Bachen
Director of I.T., Ole Hansen and Sons, Inc.

Reply to this Message

Paul Richards - 29 Mar 2006 05:53 GMT

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

>> I can’t understand why my query is returning nothing – I have set 2
>> criteria in the query design:
[quoted text clipped - 29 lines]
> WHERE (DateDepositReceived Is Not Null) AND (DateDepositLetterSent Is
> Null);

Reply to this Message

Duncan Bachen - 29 Mar 2006 15:09 GMT

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

Signature

-D
Duncan Bachen
Director of I.T., Ole Hansen and Sons, Inc.

Reply to this Message