Date / time Field with a null value

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

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
 
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#)
 
Back
Top