Solved

Set date field to null using VBA in Access

Posted on 2013-11-18
9
6,865 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

724 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