Results 1 to 9 of 9

Thread: Validate date

  1. #1
    Join Date
    Oct 2005
    Posts
    55

    Unanswered: Validate date

    Hi I have created an microsoft access database and one of the fields I have on my dataentry form is a "Date" field that takes the date as eg. Day-Month-Year.

    What I want to do with this field is to have some validation, say for example the user enter the number '32' for day an error message should show that the wrong number has been entered cause there is no 32 day in a month. Also say today is the 3rd-Nov-06 and the user entered the date as 6th-Nov-06 an error message should be generated.

    Is there a way to do this? How to do something like this, what code or validation to use?

    Thanks for the help!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if isdate(me.thisTextBox) then
    else
    endif

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Do you want the date to be today, or just a date equal to or before today?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2005
    Posts
    55
    Hi missingling the date should be equal to or before!

  5. #5
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    I don't see the problem. Set up a bit of code to set the date to Now() using the double click event. You can format the date with code to whatever you like.


  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Per Greenway

    the date should be equal to or before (today)!
    not necessarily today!

    This works for checking that the date is good and on or before today. The text box must not be formatted as date or Access' warning will popup on bad date (even with SetWarnings False). I tried to combine the two conditions with an If/Or/Then but it doesn't like the CDate function when they're combined.

    Code:
    Private Sub DateTextBox_BeforeUpdate(Cancel As Integer)
    
    If Not IsDate(Me.DateTextBox) Then
    	MsgBox "Not a Valid Date"
    ElseIf CDate(Me.DateTextBox.Value) > Date Then
    	MsgBox "This Is a FutureDate"
    End If
    
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Oct 2005
    Posts
    55
    Thanks very very much for the help the code is working to an extent.

    What I have realized is that say I entered 32-nov-06 as a date it is automatically change to 06-nov-32,I do not want that to happen.

    I want a message to be generated if a date in entered as 32-nov-06 because there is no 32 days in any given month.

    Is there a way to solve this problem? Can you provide me with the code?

    Thanks very much!

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Not sure where you're located in the world, but Windows (and hence Access) handles dates depending on the geographical settings on your PC (32-nov-06 is the Medium Date Format for the US.) This is why it's changing things around, And while I'm not sure, I believe you're sort of stuck with it, unless you go in and change your settings. The problem with that is, if I have it right, it will affect all programs on your machine! Maybe someone here will have some experience in this area!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Oct 2005
    Posts
    55
    Thanks very much for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •