is null not working for date/time

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

re: is null not working for date/time

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.

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

<jno.aubrey@gmail.com> wrote in message
news:1142522140.209090.308830@u72g2000cwu.googlegr oups.com...[color=blue]
>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
>[/color]

re: is null not working for date/time

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?

--
Wayne Morgan
MS Access MVP


<jno.aubrey@gmail.com> wrote in message
news:1142522140.209090.308830@u72g2000cwu.googlegr oups.com...[color=blue]
>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
>[/color]

re: is null not working for date/time

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.

re: is null not working for date/time

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.

--
Wayne Morgan
MS Access MVP


<jno.aubrey@gmail.com> wrote in message
news:1142527688.858636.225770@j52g2000cwj.googlegr oups.com...[color=blue]
> 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.
>[/color]