• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • Last Modified:

Need validation rule for a date entry

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
Dovberman
Asked:
Dovberman
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
use the IsDate function
0
 
TirocupidusCommented:
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
 
DovbermanAuthor Commented:
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
 
TirocupidusCommented:
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
 
DovbermanAuthor Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now