Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Thanked 0 Times in 0 Posts

    US/UK date input (Access XP)

    Hi ppl,

    I am currently developing an Access XP database and my comp system is configured for the US date system, i.e. mm/dd/yy. But the DB will eventually be used in a system that recognizes date as dd/mm/yy. I was wondering if you could let me know how I can go about evaluating and storing a date input so that it is interpreted as dd/mm/yy instead of mm/dd/yy. P.S. I cannot afford to change my own comp system to follow a dd/mm/yy format since I have other programs running that need the US format.

    Thanks for any help.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts

    Re: US/UK date input (Access XP)

    Dates in Access are actually stored as a floating point number with the integer portion representing the number of days from 31-Dec-1899 (I think), and the time being the decimal part of the number. So the real trick is to display them in the fashion they should be displayed. The challenge comes if you want to view things at the table level, and there you can run into problems, as the display part is driven based on the International format settings in the PC. I've not personally experienced problems with inconsistent dates, but I think issues arrises where people transfer when data is shipped from one system to another with a different format. A case in point might be where you have a replicated system with replicas in Quebec, Canada, a US city, a UK city and a city in Oz. Or you ship text data to a system with a different date setting that is supposed to be imported. One of our loungers in Europe or Australasia might want to comment further.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: US/UK date input (Access XP)

    Make sure that the Format property of text boxes bound to a date field is set to one of the built-in date formats like "Standard Date" or "Short Date", not to a custom format like "mm/dd/yy" or "dd-mm-yy" or "yyyy-mm-dd". The built-in formats will adapt to the Regional Settings of the user; custom formats are static. It is very confusing for the user to enter 03-08-02 and see it change to 08/03/02 when he/she tabs out of the text box.

    If you have code that creates SQL strings, make sure that dates will be in US format. For example, say you have an unbound text box txtSelectDate on a form. The user enters a date in the text box, then clicks a command button. In the code behind the command button, you assemble a SQL statement:

    strSQL = "SELECT * FROM tblOrders WHERE OrderDate = #" & [txtSelectDate] & "#"

    This will cause problems on a system with a non-US date format, because the string will contain the date as entered by the user (dd-mm-yy), but SQL will interpret is as a US date (mm/dd/yy). To get around this, I use the BuildCriteria function:

    strSQL = "SELECT * FROM tblOrders WHERE " & BuildCriteria("OrderDate", dbDate, [txtSelectDate])

    Note: some people don't trust the BuildCriteria function, but it has always worked for me in situations where I had to support users with different date settings opening the same database.

Posting Permissions

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