Solved

Need validation rule for a date entry

Posted on 2008-06-17
5
948 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

863 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

27 Experts available now in Live!

Get 1:1 Help Now