Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Locale Date Problem

    Hello again, Colleauges All,

    I have a funny little problem to do with dates in a non-US locale. In the USA, dates are expressed as mm/dd/yyyy and this is the standard in MS Access. Here in Australia, the date format is dd/mm/yyyy. This is declared in a Windows locale object which is recognised by MS Access. For instance, if I am creating a table with a date field and want to set the format property to Short Date, Access will quite happily show an example as 28/6/1999, or whatever. The only time this does not seem to work is in a SQL script. Thus, the SQL command "SELECT * FROM Sales WHERE SaleDate >= #01/08/2008# AND SaleDate <= #12/08/2008# (Australian dates) will find every record between 8th January 2008 and 8th December 2008. Well, that's OK. I have a little function which converts the Australian date into its corresponding USA equivalent and embed it into the SQL string.

    The problem I have is this. Access knows what the format of the dates in the database is, but when selecting them it appears to assume first that they are US dates and only do the conversion when the format does not match the US format. Thus, if I do a search for dates between the 25th July 2008 and 12th August 2008, it will happily display the July results correctly in form fields where the format has een set to Short Date (again with a correctly formatted example), but the August results will appear in USA format (or rather in Australian format with a wrong date assumed).

    Can anyone resolve this for me, please ?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I get this kind of problem a lot. To be on the safe side, I always specify a textual month... use a format of d mmm yyyy rather than "short date" for example.

    EG:

    SELECT * FROM Sales WHERE SaleDate >= #01 Aug 2008# AND SaleDate <= #12 Aug 2008#
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ew ST!
    How about a universal format, such as YYYY-MM-DD?
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats 'just' a feature of a US based product.... JET isn't aware of your systems localisation so it demands dates be supplied in us format (or ISO format YYYY/MM/DD) and dates need to be encapsualted by a hash symbol

    eg
    select my,column,list from mytable where mydatecolumn <= #01/13/2008#
    when representing dates I always use dd mmm yyyy
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    when representing dates I always use dd mmm yyyy
    I use d mmm yyyy, but close enough

    And yes, it would be great if the world used the same date format... yyyymmdd being most logical. It would start to resemble stardates then
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    What a pity there is no universal solution to this problem. Nevertheless, there were some excellent snippets of advice in the responses received. I am rather stuck with the dd/mm/yyyy format in the forms and the tables, because that is what the users enter when making a request and when they run their little queries. I think the VBA intercession might be the problem.
    First of all, I will do some experiments to see whether a format such as dd-mmm-yyyy is properly understood (as a date) by Jet. If so then I can store the date in that format and then I could include stuff in my SQL queries such "SELECT Format ("dd/mm/yyyy", FunctionDate) AS CurrentFunctionDate" or similar.
    Actually, this ability to include VBA functions in SQL queries which are to be processed in VBA (DoCmd.RunSQL (strSQL) and suchlike) is a very powerful concept. I cam across some code where a function was called from within a SQL query which performed some very elaborate formatting in one place and then delivered a string back with everything nicely laid out for display in a field in a report.
    Thanks everyone. I am happy to admit my limited knowledge of some of the more esoteric aspects of Access, but I am relieved to find that my question was not a complete no-brainer.
    If i may end on an aside, I note that many of the queries in this forum come from beginners or people with a limited knowledge of Access and I am very pleased (indeed touched) by the courtesy and the humour with which the undoubted gurus share there knowledge. Only once or twice have I observed a sarcastic or nastily teasing response from some smarty-pants. keep it up, guys !

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no you dont store date vakues in a format.. JET the storage engine decides what format to store dates in and its always a decimal number countng the number of ticks from (IIRC) 01/01/1900, the integer component identifies the number of days, the decimal compnent the number of ticks (IIRC) the numbr fo seconds this day, ie if 45 then time is 00:00:45, if 3600 then time is: 01:00:00

    if you are using bound controls Access (the front end) is smart enough to know its a date column, you are using a date format and t does the conversion itself between real world date formats and the US format.

    the problem is when you want to use dates in queries or you are using unbound forms in which case you generally need to specify a date in use format bracketed by a hash symbol

    http://office.microsoft.com/en-us/ac...546621033.aspx
    http://articles.techrepublic.com.com...1-6135056.html
    ..and so on

    how you format your dates is entirely up to you. there is no reason why yu cannot deploy the same access app to different parts of the world and handle dates correctly for each locale so the date on the reports/forms whatever reflects that countries representation of the date, rather than the original developers representations.. however its a long long time sine I did that, and since then I decided that my apps would always use the month name to avoid any complications

    using VBA (both standard AND user defined/developed) functions in queries is indeed a powerful tool. its also a seductive tool that leads you from the path of the righteous ([yet further] away from standard SQL). it can also be a performance hog, especially if the results of your function are used in a where clause, and or if the function isn't well designed. thats not to say don't use, but its a bit like having that special tool in the toolbox, its ideal for what it was designed for but don't over use it, dont use it inapprpriately.

    sad to say we all have off days when a wee bit of sarcasm appears....
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I agree with your sentiments, entirely, healdem, but I would like to add a caveat regarding the specification of date formats. My view on locale management does indeed suggest that you can deploy an app anywhere in the world and it will work. However, in this particular instance, I had one table where the date was actually retrieved in US format, whereas in other tables it was retrieved in the locale format. Nevertheless, I think I have some sort of take on the problem now.
    As you have said, Healdem, forms, reports, queries and other Access objects are well aware of locale management issues. Hence, when you enter data via a form and access it again via a query or report, behaviour is entirely consistent, because Access itself is talking to Jet in terms which it understands. However, if one creates a string in VBA which is an INSERT or UPDATE statement, it is only an argument to a huge function called Jet which will deal with it as it sees fit. All VBA does is to check the syntax. It does not try to second-guess you as to what you may have intended. In the other direction, it works OK, because the recordset processing knows about the locale. I have reached the conclusion that regardless of the locale, one should ALWAYS use US dates to enter data in a Jet database when using VBA. One could also be super-careful and have a function using Month(), Day() and Year() to convert back to the locale format on retieval.
    Actually, my reason for adopting this methodology is that there seems to be a bug in the Jet engine. If I offer a date in locale format (01/08/2008 i.e. 1-Aug-2008) in an SQL statement, Jet will interpret it as 8-Jan-2008. Well, OK, we understand what it is doing. However, try offering a date like 26/07/2008 which does not match a US format. One would expect Jet to declare an error. But no. It happily accepts the locale format and iterprets it correctly ! It is this inconsistency which made the solution to my problem that much more elusive.
    I hope this small insight is useful.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's an article from Allen Browne, another "down under" developer.

    http://www.allenbrowne.com/ser-36.html
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Jim Wright
    What a pity there is no universal solution to this problem.
    There is - unfortunately Jet does not respect it TMK. Google "ISO 8601".
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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