testing against an "empty" date field

  • Thread starter Thread starter John Keebaugh via AccessMonster.com
  • Start date Start date
J

John Keebaugh via AccessMonster.com

I have a couple of tables, each with several date fields (using Access 2000). I need to create a query to locate the records where a particular date field has yet to be assigned a value via one of the related forms. It seems that Access doesn't treat a date field as a value so there never is a real null date. I've tried IsNull, compared it with "", IsEmpty all to no avail. The .Net version has DateTime.MinValue available and I haven't found an equivalent in Access 2000.

Any help in successfully trapping for a yet-to-be-used date field will be greatly appreciated!
 
Access has no problems with Null dates as fields in tables: it's just Date
variables that can't accept Null values. You must specify that the field
isn't required, and you mustn't assign a default value to it.

Then, you use WHERE [MyDateField] IS NULL
 
I have a couple of tables, each with several date fields (using Access 2000). I need to create a query to locate the records where a particular date field has yet to be assigned a value via one of the related forms. It seems that Access doesn't treat a date field as a value so there never is a real null date. I've tried IsNull, compared it with "", IsEmpty all to no avail. The .Net version has DateTime.MinValue available and I haven't found an equivalent in Access 2000.

Any help in successfully trapping for a yet-to-be-used date field will be greatly appreciated!
Click to expand...

Use a criterion of

Is Null

There is a VBA function IsNull() but that's not what you need in this
case; the SQL operators IS NULL and IS NOT NULL are the only criteria
which return anything other than NULL with a null value.

John W. Vinson[MVP]
 
Back
Top