Handling Null Dates in Action Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Access 2002 code behind my maintenance form loops through a recordset of
serialized items to be fixed and writes maintenance records for those items
using action queries (like INSERT...). Sometimes the dates for all or some
maintenance/configuration information is Null: For example, if the serialized
item will not even turn on then the tech cannot get various hardware and/or
software dates. I then get "Error #3075: Syntax error in date in query
expression '##'." I need a generalized solution for handling date null
values before they get to the action query. Otherwise I have to write more
than a dozen different queries depending on which dates are missing.
 
You need to use the Nz() function to handle Nulls.

On a case-by-case basis, you need to decide how you want to handle a Null
date. You're more than likely doing date comparisons, so to return a date
that is lower that any date you're likely to encounter, use Nz(Me!txtMyDate,
0). To return a date that is higher that any date you're likely to
encounter, use Nz(Me!txtMyDate, 99999).

Using zero (0) returns Dec 30th, 1899
Using 99999 returns October 13th, 2173

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top