Re: How to over-ride US Date Format?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Baby Face Lee (BabyFaceLee_at_discussions.microsoft.com)
Date: 03/13/05 

Date: Sun, 13 Mar 2005 14:37:03 -0800

Thank you so much for the comprehensive reply. That's really helpful.
Kind regards,

Lee

"Marshall Barton" wrote:

> Baby Face Lee wrote:
>
> >Sorry to post again so soon but I'm now battling with trying to get a date
> >into UK format (dd/mm/yyyy).
> >Below is the code for the GetStartDate function that is called in a number
> >of queries as 'Between GetStartDate(x) And GetEndDate(x):
> >
> >Public datStart As Date
> >Public datEnd As Date
> >Public x As Integer
> >Public y As Integer
> >
> >Public Function GetStartDate(x As Integer) As Date 'the beginning date used
> >in most of the queries
> >
> > If x = 1 Then
> > datStart = #1/4/2004# 'summer 04
> > ElseIf x = 2 Then
> > datStart = #1/1/2005# 'january 05
> > ElseIf x = 9 Then
> > x = y
> > If x = 1 Then
> > datStart = #1/4/2004# 'summer 04
> > ElseIf x = 2 Then
> > datStart = #1/1/2005# 'january 05
> > End If
> > End If
> >
> > GetStartDate = Format(datStart, "dd/mm/yyyy")
> >
> >End Function
> >
> >The GetEndDate function is obviously very similar.
> >I've tried using FormatDateTime in the function -eg, 'datStart =
> >FormatDateTime("01/04/2004", vbShortDate) and it's still returning records
> >from 4th January instead of 1st April. How can I get this to change guys?
> >I've checked my Regional Settings and they're definitely in UK format.
>
>
> I don't have to use non-USA dates, so take this information
> with care.
>
> Rule 1 about dates: All dates inclosed in # signs must be
> in an unambiguous format (e.g. yyyy-mm-dd) or they will be
> interpreted as USA format. Your code above violates this
> when you wrote #1/4/05#, since it is ambiguous, it must be a
> USA date (4 Jan 2005).
>
> Rule 2: You can get your international locale setting to be
> used by letting Access convert a date in a string either
> automatically:
> datevariable = "1/4/05"
> or explicitly:
> variantvariable = CDate("1/4/05")
> both of which will result in 1 April 2005.
>
> But, I would feel very uncomfortable with the implicit
> conversions in your code. For either of those latter two
> approaches, you can not rely on what date you are specifying
> since moving your program to a different machine with
> different settings would convert the date strings
> differently. Therefore, I STRONGLY advise you to use the #
> syntax with an unambiguous format.
>
> Note that I am not sure what happens when you use somethng
> like:
> #1 April 2005#
> with a non English language setting.
>
> Rule 3: None of this matters to your users when entering
> data to a date field/control, since the date will be
> converted automatically using their own locale settings
> (unless they do something unusual to force the issue).
>
> --
> Marsh
> MVP [MS Access]
>

Relevant Pages

  • Re: How do I convert a string for eg 011206 to 01/12/06 in Access?
    ... Settings being a specific way. ... If the text field has the date in a given format, ... the string is in mmddyy or ddmmyy format, i.e. whether its 12 January or 1 ... Dim intYear As Integer, intMonth As Integer, intDay As Integer ...
    (microsoft.public.access.gettingstarted)
  • Re: date from different countries
    ... I want to be able to enter a date in dd/mm/yyyy format but return a true ... string, ... Dim junk As Date ... the global settings on the comptuer. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How to suppress autotext US date format after typing current y
    ... The date drop down merely reflects the language at the cursor ... American designed template to be truly UK English. ... I have all the usual settings corrected to ... the date format Word forces on you is ...
    (microsoft.public.word.docmanagement)
  • Re: Data Format on SharePoint pages - how to set
    ... \par So far, for customzing the date display format in list column, there are some solution that use a calculated column to display the customized date format. ... \par There is no setting on either the site or personal SharePoint Regional ... \par Settings for specifically setting the date format, ... \par> **Open the site on which you want to configure your regional settings. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: dates in query HELP PLEASE!
    ... > Regardless of what your regional settings have the short date format set ... > date to mm/dd/yyyy format if that makes sense. ... > "Jorge Novoa" wrote in message ... >> Then I append the where string to the rest of the SQL string, ...
    (microsoft.public.access.forms)