Date fields & null

  • Thread starter Thread starter Becky
  • Start date Start date
B

Becky

Hi - I can set a date field in a query to null, but when I
try to do that in code I get Invalid use of null. Is
there any way to set a date field to null in code?

Thanks!
 
My guess is that you are trying to set a _variable_ to Null in code, and it
is type Date/Time. The only type of variable that can be set to Null is the
Variant type. A Control can be set to Null, and, generally at least, so can
Fields, but variables are limited as described.

Larry Linson
Microsoft Access MVP
 
Becky,

You can set a DateTime field to Null, but if you have code that specifically
tests for a date, yet gets a Null, you will run into the error you describe.
In such cases, you need to explicitly test for Null, and supply a valid date
if one is found. For example, the following code uses the Nz() function to
supply a date if the field contains a Null value.

Nz(Me.txtDate, 999999)

999999 produces a date of 25 November 4637, which you can reasonably expect
to be well beyond the lifetime of your database. Similarly, a value of -1
produces a date of 29 December 1899 (a little before your time). Depending
on what you're trying to do, you can test to see if the textbox's date is
before, after or between a specific date range. Either of the above values
will fail this test, and thus, give you something that you can work with,
without error.

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