Handling Null Dates in Action Queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bobbat - 04 May 2005 14:48 GMT

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.

Signature

Bob B.

Reply to this Message

Graham R Seach - 04 May 2005 14:59 GMT

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

> The Access 2002 code behind my maintenance form loops through a recordset
> of
[quoted text clipped - 11 lines]
> more
> than a dozen different queries depending on which dates are missing.

Reply to this Message