Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Year Entry (Access 2000 and XP)

    Is there a bug in Access that allows you to enter a date as May 04, 200 and then it will switch it over to 04/05/200? You seem to be able to enter a 3 digit year.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Year Entry (Access 2000 and XP)

    Why would it be a bug? The valid range of years is 100 - 9999; if you enter a number in the range of 0 - 29, it will be interpreted as 2000 - 2029, and if you enter a number in the range of 30 - 99, it will be interpreted as 1930 - 1999.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Year Entry (Access 2000 and XP)

    That actually happens as a result of the switch between Gregorian and Julian calendars that occurred in the 16th century (I think). Dates prior to 12/31/1899 are stored as a negative number, 12/31/1899 to whatever the max date in 9999 is are stored as postive numbers. And yes, you can enter three digit years - it's a common type in entering dates, and you may want to put in validation code for such things.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year Entry (Access 2000 and XP)

    Thanks. What would the validation code look like?
    Thank again.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Year Entry (Access 2000 and XP)

    There are several possibilities. Say that you want to accept dates between 1/1/1950 and 12/31/2002.

    You could enter Between #1/1/1950# And #12/31/2002# in the Validation Rule property of the field in the table, or of the control bound to that field in a form. Enter an appropriate warning text in the Validation Text property, too.

    Alternatively, you can perform your validation in the Before Update event of the control bound to the date field, and set its Cancel argument to True if the date is outside the permitted range. For a text box txtDate it could look like this:

    Private Sub txtDate_BeforeUpdate(Cancel As Integer)
    If CDate(txtDate) < #1/1/1950# Or CDate(txtDate) > #12/31/2002# Then
    MsgBox "Date outside permitted range (1950 through 2002)", vbExclamation
    Cancel = True
    End If
    End Sub

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year Entry (Access 2000 and XP)

    Thanks so much - it worked great.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year Entry (Access 2000 and XP)

    One slight note to Han's answer is that you can use the Date() function in Validation as well. I regularly use "<=Date()" to prevent a future date being added in Data Entry forms

    HTH

    Peter

Posting Permissions

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