Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    European date input (Access2003)

    I am working on a database for Europe and having trouble formatting date fields. Was wondering if you could help me.

    For example, when inputting the date in non-American format (e.g., 21/10/08 for 21 October 2008), the field formats to 8/10/2021.

    Do you know how to set-up the type of date field I need?

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

    Re: European date input (Access2003)

    If your own system is set to US date format mm/dd/yy, it will misinterpret 21/10/08. You should enter 10/21/08. But if someone whose system is set to dd/mm/yy format enters 21/10/08, it wil be interpreted correctly.

    In short, you don't have to worry about the way the user enters a date - let his or her version of Windows figure it out according to the local system settings. For this reason, I recommend *not* setting an input mask on date fields/controls - it will lead to confusion and frustration.

    To avoid confusion over the meaning of a displayed date (does 08/10 mean 10 Augustus or 8 October?), you could set the Format property of date fields and the controls bound to them to dd-mmm-yyyy. In this format, you'd see 10-Aug-2008 or 08-Oct-2008. This leaves no room for ambiguity.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: European date input (Access2003)

    I see...Date field in my table is set as Date/Time. When I am entering 21/10/2008 it is automatically changing it to 10/21/2008.

    So where am I suppose to set this formula data =Format([DateOpened],"dd/mm/yyyy") (or another one) for date in my table to stay put?


    To make it clearer - how and where do I "Format property of date fields and the controls bound to them to dd-mmm-yyyy"? Thanks

    P.S. I am manually entering date in table in Euro format.

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

    Re: European date input (Access2003)

    Access stores dates as a number - the number of days since December 30, 1899. So for example today (October 22, 2008) is stored as 39743. This value is completely independent on the format used to enter or display dates.
    When you enter a date, you type a text string. Someone in the USA would type 10/22/08, I (in The Netherlands) would type 22-10-2008, and someone in Lithuania would type 2008.10.22.
    Access then asks Windows to convert this text string to a number using the system setting for dates. In all three examples, the result would be 39743, and that's how Access stores the date.
    If you haven't specified an explicit display format, Access will display the date according to the local setting.
    So if I enter 22-10-2008, Access stores 39743 but displays 22-10-2008 on my monitor. If I send the database to you, and you open it, you will see 10/22/08 on your screen. The actual value hasn't changed - it is still 39743 - but since you're in the US, Access displays the date using US date format.
    Conversely, if you enter 07/04/76 in a date/time field, Access converts it to the value 27945 and stores that in the table. If you send the database to someone in Lithuania, that person will see 1976.07.04 on his or her screen. It is exactly the same date, but it is displayed in a different way.

    So you should NOT, I repeat NOT, use a formula such as Format([DateOpened],"dd/mm/yyyy") to STORE a date in the database. You should let Access and Windows do their own thing and store the date in a format-independent way.

    When you create a date/time field in a table, one of the properties in the lower pane of the table design window is named Format. You can select from various pre-defined formats such as Short Date and Long Date, and you can also type a custom format yourself. The Format property has NOTHING to do with the way the dates are stored in the table. The Format property determines how the date is displayed on the screen and in printed output.
    Similarly, if you place a text box on a form or report, the text box has a Format property (in the Format tab of the Properties window). Again, this property only determines how the date value is displayed on the screen and in printed output.
    I suggested entering dd-mmm-yyyy in the Format property, because that results in a display that is unambiguous: the day is displayed using 2 digits, the month using the abbreviated name, and the year using 4 digits. So there can't be any confusion between day, month and year.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: European date input (Access2003)

    I must be dumb!
    I do not understand what should I do.
    I did entered dd-mmm-yyyy into Format of the lower pane of the table design window.
    However I am inserting 21/10/08 into the field and it is changing to a 08-Oct-2021.

    We are creating database for our offices in Europe but we are going to use it here is US.
    Is there way? Thanks

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

    Re: European date input (Access2003)

    It doesn't matter that you're creating a database for European offices. You should simply enter the date the way you usually do: 10/21/08. This will be displayed as 21-Oct-2008, both for you and for users in Europe. Users in Europe would enter the same date as 21/10/2008 or as 21-10-08 or as 2008.10.21 - whatever their local system is. It will still be displayed as 21-Oct-2008.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: European date input (Access2003)

    OK, now I am scared!:-)
    You are corect of course - 10/21/08 is displayed as 21-Oct-2008
    Now I am wondering why?
    I had tried 10/11/2008 and it displayed as 11-Oct-2008.
    Why not Oct-11-2008 as we would read here is US? I had not set any formats, I had just chosen Medium Date.

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

    Re: European date input (Access2003)

    "Medium Date" is equal to dd-mmm-yyyy, whether you're in the USA or in Europe. It's a kind of "international" format that everybody can use, unlike "Long Date" and "Short Date" which are more specific to the country settings.

    I've mentioned it before, and I'll repeat it here: everyone can enter dates the way they are used to; Windows takes care of interpreting the date correctly according to the user's local settings.
    If you would like to know how your database looks to a user in the UK, you can do the following:
    - Quit Access.
    - Open the Regional Settings control panel.
    - Select English (United Kingdom) from the dropdown list, then click OK.
    - Open your database and enter a date. You'll see that 10/11/2008 is now displayed as 10-Nov-2008.
    - When you're done, quit Access, and set the regional settings back to English (United States).

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: European date input (Access2003)

    Thanks and I am passing this onto Europeans to see if it is going to work for them.

    I had learned A LOT today! Thanks, Hans.

Posting Permissions

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