Solved

Need validation rule for a date entry

Posted on 2008-06-17
5
946 Views
Last Modified: 2013-11-28
I would like to assure that the user enters a date more than 30 days in the past and is a valid date format.

<=Date()-30 works to prevent dates more recent than the past 30 days.

What is the combined expression to make sure the date is an actual date. Not something like 55/13/2003
or ab/23/200a ?

Is it something like <=Date()-30 AND .....

Thanks,
0
Comment
Question by:Dovberman
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21807135
use the IsDate function
0
 
LVL 4

Expert Comment

by:Tirocupidus
ID: 21807182
I would check to make sure the text is a valid date before checking it against what you know is a valid date.
If IsDate(text) Then

   If text <= Date() - 30 Then

      'date is good

   Else

      'date is not good (last 30 days)

   End If

Else

   'date is not valid

End If

Open in new window

0
 

Author Comment

by:Dovberman
ID: 21807764
I would like to use the validation rule property of the text box.

something like ValidationRule= <=Date()-30  AND IsDate

Is this possible ?

I like the validationrule property, because it keeps the user in the textbox until the entry meets the rule.

Thanks
0
 
LVL 4

Accepted Solution

by:
Tirocupidus earned 250 total points
ID: 21814429
Using the Validation Rule, it seems to work fine with just "<=Date()-30". It won't let you out if you put in a bogus date.

If you choose to validate in the code, you can provide detailed error messages. In the code I posted, you can display message box stating that either the date isn't a valid date, or the date is within the last 30 days, depending on the location in the If statement. You would put this in the BeforeUpdate event and set Cancel = True if you want to stay in the box.
Private Sub textbox_BeforeUpdate(Cancel As Integer)

   If IsDate(textbox) Then

      If textbox <= Date - 30 Then

         'date is good

      Else

         'date is not good (last 30 days)

         MsgBox "Date needs to be prior the past 30 days."

         Cancel = True

      End If

   Else

      'date is not valid

      MsgBox "The date you entered isn't valid."

      Cancel = True

   End If

End Sub

Open in new window

0
 

Author Comment

by:Dovberman
ID: 21815745
I am never sure if the user will dirty the control. I like the simple validation rule "<=Date()-30".

I check for missed entries in the Click property of a Save button.

Required controls have a tag that is tested. If the tag is a null string, the control is not required. Other wise a message box displays the tag value("You need to enter a value for Address"). Then the missed control gets the focus and is set to a yellow background.

Thanks,
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 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

20 Experts available now in Live!

Get 1:1 Help Now