'saving' null Date

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have an unbound form from which data entered by user is saved to various
tables when user clicks on 'save' command buttons. Among the fields are
several date fields that do not have to be entered in all circumstances. I
use an SQL statement & docmd.runsql but if date field remains null, I get an
error about a field not being saved. I tried Nz function but I don't think
this works with dates. Any suggestions on how to avoid error would be
gratefully accepted. Thanks.
 
Two suggestions:
a. modify the column to allow nulls
b. Use a default value( 1/1/1900 is most common) - either in the table or in
the form

Although it sounds odd, using a default value is the preferred method.

HS
 
Your SQL string needs to contain the word Null when no value has been
supplied:

strSQL = "INSERT INTO MyTable (Field1, Field1) SELECT " _
& Nz(Text1, "Null") & " AS Field1, " & Nz(Text2, "Null") & " AS Field2;"
 
Back
Top