[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Set date field to null using VBA in Access

Posted on 2013-11-18
9
Medium Priority
?
7,414 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 31

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 31

Accepted Solution

by:
hnasr earned 2000 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 31

Expert Comment

by:hnasr
ID: 39663175
Welcome!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

656 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