Set date field to null using VBA in Access
Posted on 2013-11-18
How do I set a date to nothing? For example, a table with a date field initially has nothing, but once a user selects a date, I cannot set it back to blank in VBA (only if user utilizes a form to delete the date.) I have read that only a variant type can be set to null, but I don't know how to utilize this information to update the actual date field.
Private Sub DATE_COMPLETED_FOR_RELEASE_BeforeUpdate(Cancel As Integer)
Dim dateCompleted As Variant
dateCompleted = Me.DATE_COMPLETED_FOR_RELEASE.Value
If Non_Work_Days(dateCompleted) = False Then
MsgBox "Select a work day, not a Saturday, Sunday or holiday."
dateCompleted = Null
Set Me.DATE_COMPLETED_FOR_RELEASE = dateCompleted
Note the code for Non_Work_Days() does work fine and is as below:
Public Function Non_Work_Days(NonBusinessDays As Variant) As Boolean
Dim VarA As Variant
Dim BoolY As Boolean
Dim BoolZ As Boolean
VarA = IsNull(DLookup("Holiday", "t_Holiday_Dates", "Holiday=#" & NonBusinessDays & "#"))
BoolY = Format(NonBusinessDays, "ddd") <> "Sat"
BoolZ = Format(NonBusinessDays, "ddd") <> "Sun"
If VarA And BoolY And BoolZ = True Then
Non_Work_Days = True
Non_Work_Days = False