Date check in VBA (1 Viewer)

PaquettePaul

Member
Local time
Today, 07:37
Joined
Mar 28, 2022
Messages
107
Quick quesrrion regarding date validation. i looked for an answer but found zilch.

User enters a date and in the Before Update process in the form, I check to see if the specified date is greater than the current date or more than a year in the past. Also want to store the results from the date picker in date only format. Solution would be great.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:37
Joined
Oct 29, 2018
Messages
21,358
Not sure I understand what you're asking. What have you tried? What happened? Is it an unbound Textbox?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:37
Joined
Feb 28, 2001
Messages
27,001
First, dates are not stored as dates anyway. They are stored as a typecast, or you might call it an alternate interpretation, of type DOUBLE. When dealing with dates, you hardly EVER store them as anything other than the DATE data type. The key is not the data type, but the default data format.

The "BeforeUpdate" event includes a Cancel option, so you can pass back a value of -1 (as TRUE) or 0 (as FALSE) to tell Access whether you want to cancel the update.


EDIT: Added link to event description.

Checking dates is easy. You want to find if the date is future?

IF Date() < CDate( Me.UserDate) THEN .... and do whatever you wanted to do for future dates.

Then, you want more than a year in the past?

IF ( Date() - CDate( Me.UserDate ) ) > 365 THEN ... and do whatever you wanted for dates more than a year old.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:37
Joined
Sep 21, 2011
Messages
14,051
Quick quesrrion regarding date validation. i looked for an answer but found zilch.

User enters a date and in the Before Update process in the form, I check to see if the specified date is greater than the current date or more than a year in the past. Also want to store the results from the date picker in date only format. Solution would be great.
Here you go.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:37
Joined
Feb 19, 2002
Messages
42,981
Also want to store the results from the date picker in date only format
Not sure what that means. As the others have mentioned, dates are NOT strings. they are double precision numbers. And that is how you want to store them as the date data type so that Access recognizes they are dates and knows how to work with them. If you store them as strings, Access will treat them as strings and they will not sort or compare correctly.
Code:
    If Me.YourDate > Date() Then
        Msgbox "date is in the future.  do something"
        Cancel = True
        Exit Sub
    Else
        if Me.YourDate < DateAdd("y", -1, Date()) Then
            Msgbox "date is more than one year in the past. do something"
            Cancel = True
            Exit Sub
        End If
    End If
 

Users who are viewing this thread

Top Bottom