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
Solved

Need validation rule for a date entry

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

839 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