Set date field to null using VBA in Access

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
        End If
End Sub


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
    Else
    Non_Work_Days = False
    End If

End Function
grmcraAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Add Exit event and modify BeforeUpdate and Exit events code as follows:

Private Sub DATE_COMPLETED_FOR_RELEASE_BeforeUpdate(Cancel As Integer)
End Sub

Private Sub DATE_COMPLETED_FOR_RELEASE_Exit(Cancel As Integer)
    Dim dateCompleted As Date
    Dim vd As Variant
    vd = Null
    If Not IsNull(Me.DATE_COMPLETED_FOR_RELEASE.Value) Then
        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."
            Me.DATE_COMPLETED_FOR_RELEASE = vd
        End If
    End If
End Sub

Open in new window

Expert-Exchange-Version-2.accdb
0
 
mbizupCommented:
Skip the step that uses the variable, and drop the 'set':

       '  dateCompleted = Null
         Me.DATE_COMPLETED_FOR_RELEASE = NULL

Open in new window

0
 
grmcraAuthor Commented:
Hi mbizup,

I received the attached screenshot message after I changed the code to the following:

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."
       Me.DATE_COMPLETED_FOR_RELEASE = Null
        End If
End Sub

The message box fires, but it gets stuck on Me.DATE_COMPLETED_FOR_RELEASE = Null

I have a validation rule on the field:  [DATE_COMPLETED_FOR_RELEASE]>=DateAdd("d",-1,[Entry_Date]) Or Is Null

Would that cause an issue?
Capture.JPG
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
mbizupCommented:
Try rewriting the rule like this:

 [DATE_COMPLETED_FOR_RELEASE]>=DateAdd("d",-1,[Entry_Date]) Or IsNull([DATE_COMPLETED_FOR_RELEASE]) = True

Open in new window

0
 
mbizupCommented:
Or this:

NZ([DATE_COMPLETED_FOR_RELEASE], #1/1/1900#) >=DateAdd("d",-1,[Entry_Date])

Open in new window


where 1/1/1900 is just an arbitrary very early date, which would not appear in your actual data, to replace nulls
0
 
hnasrCommented:
Try:

Dim vd as Variant
vd = Null

then use vd to set date field to Null
txtDate = vd
0
 
grmcraAuthor Commented:
Hi Experts,

I have tried the above, but changing to either options from mbizup still gives me the error message and changing to hnasr's method seems to also give me the error (perhaps I'm not doing it right though).  I also noticed that if I try to delete the date on the form, it gives me the same error message.

I have attached a stripped version of the database in case there is something else I am missing that I should be disclosing.  The form is 0_Form and the data field on the form is circled in red in the attached screen shot.

Thanks for any more help : )
Capture.JPG
Expert-Exchange-Version.accdb
0
 
grmcraAuthor Commented:
Awesome, thank you!!!
0
 
hnasrCommented:
Welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.