Date check in VBA

[PaquettePaul]

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]

Not sure I understand what you're asking. What have you tried? What happened? Is it an unbound Textbox?

 

[The_Doc_Man]

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.

Form.BeforeUpdate event (Access)

Office VBA reference topic

docs.microsoft.com

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]

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.

Google Search

 

[Pat Hartman]

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.

    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