is null not working for date/time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jno.aubrey@gmail.com - 16 Mar 2006 16:15 GMT

I have a Access 97 query that has in its where clause:

And ((tbl_01_LoanData.PayOffDate) Is Null)

Upon execution, all records returned have no PayOffDate except 1. When
I include PayOffDate in the select list one record shows a date of
'12/16/2005' - clearly not a null value. Shouldn't the above code
filter this out? Many thanks!

-Jeff

Reply to this Message

Allen Browne - 16 Mar 2006 17:26 GMT

Yes, it should filter it out, though there could be something else in the
WHERE clause that is affecting it.

For example, if you have:
WHERE (Company Is Null) OR (Amount > 0 AND PayOffDate Is Null)
any record where the Company field is null will be returned, regardless of
the date. That's becuase of the way the OR and AND are bracketed.

In summary:
A OR (B AND C)
is not the same as:
(A OR B) AND C

If that is not what is happening, try a repair on your database. If the
PayOffDate field is indexed, a corrupt index can give the symptoms you
describe.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a Access 97 query that has in its where clause:
>
[quoted text clipped - 6 lines]
>
> -Jeff

Reply to this Message

Wayne Morgan - 16 Mar 2006 17:34 GMT

By chance is that record the last one? If so, could it be that what you're
seeing is the DefaultValue for the field in the row for a new record?

Signature

Wayne Morgan
MS Access MVP

>I have a Access 97 query that has in its where clause:
>
[quoted text clipped - 6 lines]
>
> -Jeff

Reply to this Message

jno.aubrey@gmail.com - 16 Mar 2006 17:48 GMT

The full query is as follows:

SELECT tbl_01_LoanData.LoanNumber, [tbl_01_LoanData]![BorrowerLast] &
', ' & [tbl_01_LoanData]![BorrowerFirst] AS Borrower,
tbl_01_LoanData.NoteAmount, tbl_01_LoanData.InvestorCompany,
tbl_01_LoanData.ClosingDate, tbl_01_LoanData.DisbursementDate,
tbl_01_LoanData.LesserAmount, tbl_01_LoanData.PayOffDate
FROM tbl_01_LoanData
WHERE (((tbl_01_LoanData.ClosingDate) Is Not Null) And
((tbl_01_LoanData.DisbursementDate) <=#3/16/2006#) And
((tbl_01_LoanData.LoanType) = 'Warehouse') And
((tbl_01_LoanData.PayOffDate) Is Null) And
((tbl_01_LoanData.LoanStatus) = 'In Process'))
ORDER BY tbl_01_LoanData.DisbursementDate;

The corrupt database diagnosis could be correct. The offending record
does meet all of the other criteria in the where clause, but the null
check for PayOffDate should filter it out.

Reply to this Message

Wayne Morgan - 17 Mar 2006 14:04 GMT

The logic of the WHERE clause appears correct. Assuming you're not just
mistaking the default value in the new record, then I would go with Allen's
suggestion of a corrupt index. When you do a Compact and Repair, it will
redo the indexes. See if that solves the problem.

Signature

Wayne Morgan
MS Access MVP

> The full query is as follows:
>
[quoted text clipped - 14 lines]
> does meet all of the other criteria in the where clause, but the null
> check for PayOffDate should filter it out.

Reply to this Message