Selecting a null field 1

[jphillips]

Hello,

I am trying to select a null or empty date field.
I have tried writing a sql in access

select * from table_name where date_field is null;

or

select * from table_name where date_field = '';

How can I retrieve this info??

 

[PHV]

Are you sure that date_field allow null ?
you may try this:
select * from table_name where Nz(date_field,0)=0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jphillips]

Didn't work. The field is supposed to be a date field called completed_date. I just want to find all the orders where the date has not been entered. Very simple query in Oracle. Not sure about this MS Access

 

[cmmrfrds]

Try.

select * from table_name where date_field is null
or len(ltrim(date_field))=0

If this work then it is either null, empty, or blank. You can then check which one.

 

[jphillips]

ok. that worked. It also helps to use the correct column name also.(complete_date vs. completed_date.
THanks for the help.