Solved

Set date field to null using VBA in Access

Posted on 2013-11-18
9
6,352 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

815 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

7 Experts available now in Live!

Get 1:1 Help Now