Solved

Set date field to null using VBA in Access

Posted on 2013-11-18
9
6,263 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:grmcra
  • 3
  • 3
  • 3
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39657378
Skip the step that uses the variable, and drop the 'set':

       '  dateCompleted = Null
         Me.DATE_COMPLETED_FOR_RELEASE = NULL

Open in new window

0
 

Author Comment

by:grmcra
ID: 39657539
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39657727
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39657736
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 30

Expert Comment

by:hnasr
ID: 39659448
Try:

Dim vd as Variant
vd = Null

then use vd to set date field to Null
txtDate = vd
0
 

Author Comment

by:grmcra
ID: 39660750
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
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 39661301
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
 

Author Closing Comment

by:grmcra
ID: 39662833
Awesome, thank you!!!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39663175
Welcome!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now