Date / time Field with a null value

[Guest]

Hi, I have a form in which there is an opening and closing date along with
other info. When completed the user is to hit the update button to update
the info.
The issue is that the date closed field is optional, it can be filled in or
left blank. However since the date type for this field is date/time, when I
try and add the records to the database if the user has left the field blank,
I get a "type mismatch" how can I keep my data type and date/time but also
allow null values ?
Thanks.
Todd

Dim RecordToInsert As New ADODB.Recordset
Call RecordToInsert.Open("tblpurchaseorders",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic)
With RecordToInsert
.AddNew
.Fields("intpurchaseorder") = Me.intPurchaseOrder
.Fields("dtmdateopen") = Me.dtmDateOpen
.Fields("dtmdateclosed") = Me.dtmDateClosed
.Fields("strDriver") = Me.strDriver
.Fields("MemNotes") = Me.MemNotes
.update

 

[Douglas J. Steele]

You have to check for Null values and either replace them with a default, or
prompt the user for a real value.

If you want to use a default, you can use the Nz function:

.Fields("dtmdateclosed") = Nz(Me.dtmDateClosed, #9999-12-31#)