Results 1 to 10 of 10
  1. #1
    independent
    Guest

    Date Validation Problem

    This applies to both 97 SR-2 and 2000.
    I am outside US, so short date format is dd/mm/yy.
    When 31/02/01 is entered into a date field, Access takes it as 1 Feb 1931. Short of writing my own date validation routine has anyone any suggestions.

    Thanks

    Mark Wheeler

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date Validation Problem

    Hi Mark,
    I don't know of any simple way of preventing Access from being 'helpful' in this way, except perhaps forcing your users to enter 4-digit years. However, if you want any kind of meaningful warning message when they enter an invalid date, I think you'll have to go with your own validation routine.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Validation Problem

    Do you mean your database will be used in various countries? If it is just in one country then have a look at the settings of the PC's it runs on.
    I use both English and German versions of Access in Switzerland (day/month/year) and have not experienced this 'problem' with Access 2, 95, 97 or 2000 (XP - not got it yet)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date Validation Problem

    Hi Andy,
    I've got Access 2000 on my PC, regional settings showing ShortDate as dd/mm/yy, and it does exactly what is described. It works perfectly if you enter a valid date, but if you enter an invalid one like 31/02/01, Access does try to convert it to a valid one. Does this not happen on yours?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Validation Problem

    Since 31 Feb 2001 is not a valid date, Access looked for a combination that is valid and concluded that the user entered yy/mm/dd. I think that the only way to prevent that is to write code to do your own validation.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Validation Problem

    Yes, it sure would be nice to be able to turn off Access's "second guessing"! IMHO it increases data-entry errors rather than decreasing them.

    In any case, are all dates valid in your database or can you put in a validation rule to check for sensible years? That's simpler than parsing dates yourself.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date Validation Problem

    Hi Douglas,
    It's not actually my database that has the problem. I am in the probably enviable position of mostly building databases for my own use and in those rare cases where I have to let others use them, they're generally importing data from elsewhere so I don't have to worry about user input at least!
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Validation Problem

    [img]/S/blush.gif[/img][img]/S/blush.gif[/img][img]/S/blush.gif[/img][img]/S/blush.gif[/img][img]/S/blush.gif[/img][img]/S/blush.gif[/img][img]/S/blush.gif[/img]

    I'd not seen the date was invalid. (Cringe , cringe, find hole to go and hide in)

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Validation Problem

    Just to agree that the way access does this is a total PITA. I had a query about this on the old forum that Douglas and Legare contributed to and after much hair pulling I gave up and just lived with a basic year validation to pick up the worst errors.

  10. #10
    DAW
    Guest

    Re: Date Validation Problem

    I hate the auto-correct-date functionality that Access forces upon you. I wrote the following simple function to override it.

    Public Function CheckDate(frm As Form) As Boolean
    On Error GoTo CheckDate_Err
    'Call this function from a date field's BeforeUpdate event like this:- '
    'Cancel = CheckDate(Me)
    '
    'NB: Set the date field's input mask to 99/99/0000;0;_

    Dim strDate1 As String
    Dim strDate2 As String
    Dim TxtBox As TextBox

    Set TxtBox = frm.ActiveControl

    strDate1 = TxtBox.Text
    strDate2 = Format(TxtBox.Text, "dd/mm/yyyy")

    If StrComp(strDate1, strDate2) <> 0 Then
    CheckDate = True
    MsgBox "Please enter a valid date", vbCritical, "Validation Error!"
    Else
    CheckDate = False
    End If

    CheckDate_Exit:
    Exit Function
    CheckDate_Err:
    ErrorLog conModuleError, "CheckDate", Err.Number, Err.Description
    Resume CheckDate_Exit

    End Function


    HTH

    Dave.

Posting Permissions

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