Solved

Need validation rule for a date entry

Posted on 2008-06-17
5
962 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
[X]
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
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

724 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