Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Date format

Discussion in 'Information Technology' started by Elaine, Jul 28, 2006.

Tags:
  1. Elaine

    Elaine
    Expand Collapse
    Guest

    Hi guys,
    I'm having some problems with date formatting.

    Background:
    - My system regional settings is set to UK system
    - Location is set to United Kingdom
    - All my dates in my Access tables are formatted as "dd/mm/yyyy"
    - MS Windows XP SP2
    - MS Access 2003

    I have a table called tblSemester with the following information:
    Field names: Semester, semStartDate, semEndDate
    Sample data: S10506,01/10/2005,27/01/2006
    S20506,28/01/2006,09/06/2006
    Summer0506,10/06/2006,30/09/2006
    S10607,01/10/2006,29/01/2007
    S20607,30/01/07,02/06/2007

    I have the following function:
    ************************
    Public Function setSemester() As String
    Dim varCriteria As String
    varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
    [semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
    varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
    setSemester = varSemester
    End Function
    ***************************

    And finally, I have a form with one control, txtSemester, where the Default
    value is set to "setSemester"

    My problem is:
    the result of the function is always wrong as it assumes the date is in the
    American format.
    For example, if today's date is 09/05/2006 (9 May 2006), the function will
    return "Summer0506"(as would be expected if today's date were 05/Sept/2006),
    instead of "S20506" as it should.
    Could you please let me know where else in the system I should set the
    location to UK, or any other way to work around this problem.

    Let me know if you need other details.

    Many many thanks,

    Elaine
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Despite the fact that your regional settings may have your Short Date format
    set to dd/mm/yyyy, you must use mm/dd/yyyy in queries.

    Change your code to use

    varCriteria = "[semStart] <= " & Format(Date, "\#mm\/dd\/yyyy\#") & "
    AND [semEnd] >= " & Format(Date, "\#mm\/dd\/yyyy\#")

    (Actually, dd/mm/yyyy format will work, but only if the day is 13 or higher:
    in that case, Access will realize that there is no 13th month and interpret
    it correctly. Also, it's not strictly true that you must use mm/dd/yyyy: you
    can use an unambiguous format such as yyyy-mm-dd or dd mmm yyyy.)

    You might find it interesting to read Allen Browne's "International Dates in
    Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
    had in my September 2003 Access Answers column for Pinnacle Publication's
    "Smart Access" newsletter. The column and accompanying database can be
    downloaded at http://members.rogers.com/douglas.j.steele/SmartAccess.html


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Elaine" <elaine_ellul@yahoo.co.uk> wrote in message
    news:u6f603BdGHA.3952@TK2MSFTNGP04.phx.gbl...
    > Hi guys,
    > I'm having some problems with date formatting.
    >
    > Background:
    > - My system regional settings is set to UK system
    > - Location is set to United Kingdom
    > - All my dates in my Access tables are formatted as "dd/mm/yyyy"
    > - MS Windows XP SP2
    > - MS Access 2003
    >
    > I have a table called tblSemester with the following information:
    > Field names: Semester, semStartDate, semEndDate
    > Sample data: S10506,01/10/2005,27/01/2006
    > S20506,28/01/2006,09/06/2006
    > Summer0506,10/06/2006,30/09/2006
    > S10607,01/10/2006,29/01/2007
    > S20607,30/01/07,02/06/2007
    >
    > I have the following function:
    > ************************
    > Public Function setSemester() As String
    > Dim varCriteria As String
    > varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
    > [semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
    > varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
    > setSemester = varSemester
    > End Function
    > ***************************
    >
    > And finally, I have a form with one control, txtSemester, where the

    Default
    > value is set to "setSemester"
    >
    > My problem is:
    > the result of the function is always wrong as it assumes the date is in

    the
    > American format.
    > For example, if today's date is 09/05/2006 (9 May 2006), the function will
    > return "Summer0506"(as would be expected if today's date were

    05/Sept/2006),
    > instead of "S20506" as it should.
    > Could you please let me know where else in the system I should set the
    > location to UK, or any other way to work around this problem.
    >
    > Let me know if you need other details.
    >
    > Many many thanks,
    >
    > Elaine
    >
    >
    >
    >
    >
    >
    >
    >
     
  4. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Elaine:

    Date literals in Access must be in US or an otherwise internationally
    unambiguous format. While dates are stored as a 64 bit floating point number
    regardless of the local system date format, they are expressed by default in
    the local short date format. Change your function to the following and it
    should be fine:

    ************************
    Public Function setSemester() As String

    Dim varCriteria As String
    varCriteria = "semStart <= #" & Format(Date, "mm/dd/yyyy") & _
    "# AND semEnd >= #" & Format(Date, "mm/dd/yyyy") & "#"

    setSemester = DLookup("Semester", "tblSemester", varCriteria)

    End Function
    ***************************

    As this is a common requirement I have the following function in a standard
    module:

    Function USDate(varDate)

    If Not IsNull(varDate) Then
    USDate = "#" & Format(varDate, "mm/dd/yyyy") & "#"
    End If

    End Function

    It can then be called when necessary, e.g. in your case:

    varCriteria = "semStart <= " & USDate(Date) " & _
    "AND semEnd >= " & USDate(Date)

    Ken Sheridan
    Stafford, England

    "Elaine" wrote:

    > Hi guys,
    > I'm having some problems with date formatting.
    >
    > Background:
    > - My system regional settings is set to UK system
    > - Location is set to United Kingdom
    > - All my dates in my Access tables are formatted as "dd/mm/yyyy"
    > - MS Windows XP SP2
    > - MS Access 2003
    >
    > I have a table called tblSemester with the following information:
    > Field names: Semester, semStartDate, semEndDate
    > Sample data: S10506,01/10/2005,27/01/2006
    > S20506,28/01/2006,09/06/2006
    > Summer0506,10/06/2006,30/09/2006
    > S10607,01/10/2006,29/01/2007
    > S20607,30/01/07,02/06/2007
    >
    > I have the following function:
    > ************************
    > Public Function setSemester() As String
    > Dim varCriteria As String
    > varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
    > [semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
    > varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
    > setSemester = varSemester
    > End Function
    > ***************************
    >
    > And finally, I have a form with one control, txtSemester, where the Default
    > value is set to "setSemester"
    >
    > My problem is:
    > the result of the function is always wrong as it assumes the date is in the
    > American format.
    > For example, if today's date is 09/05/2006 (9 May 2006), the function will
    > return "Summer0506"(as would be expected if today's date were 05/Sept/2006),
    > instead of "S20506" as it should.
    > Could you please let me know where else in the system I should set the
    > location to UK, or any other way to work around this problem.
    >
    > Let me know if you need other details.
    >
    > Many many thanks,
    >
    > Elaine
    >
     
  5. Elaine

    Elaine
    Expand Collapse
    Guest

    Dear Doug,

    This is absolutely perfect, it works a treat! Many many thanks!
    Also thanks for the links for more info on date issues.

    Kind Regards,
    Elaine


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:eBaTNHCdGHA.4592@TK2MSFTNGP04.phx.gbl...
    > Despite the fact that your regional settings may have your Short Date
    > format
    > set to dd/mm/yyyy, you must use mm/dd/yyyy in queries.
    >
    > Change your code to use
    >
    > varCriteria = "[semStart] <= " & Format(Date, "\#mm\/dd\/yyyy\#") & "
    > AND [semEnd] >= " & Format(Date, "\#mm\/dd\/yyyy\#")
    >
    > (Actually, dd/mm/yyyy format will work, but only if the day is 13 or
    > higher:
    > in that case, Access will realize that there is no 13th month and
    > interpret
    > it correctly. Also, it's not strictly true that you must use mm/dd/yyyy:
    > you
    > can use an unambiguous format such as yyyy-mm-dd or dd mmm yyyy.)
    >
    > You might find it interesting to read Allen Browne's "International Dates
    > in
    > Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
    > had in my September 2003 Access Answers column for Pinnacle Publication's
    > "Smart Access" newsletter. The column and accompanying database can be
    > downloaded at http://members.rogers.com/douglas.j.steele/SmartAccess.html
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Elaine" <elaine_ellul@yahoo.co.uk> wrote in message
    > news:u6f603BdGHA.3952@TK2MSFTNGP04.phx.gbl...
    >> Hi guys,
    >> I'm having some problems with date formatting.
    >>
    >> Background:
    >> - My system regional settings is set to UK system
    >> - Location is set to United Kingdom
    >> - All my dates in my Access tables are formatted as "dd/mm/yyyy"
    >> - MS Windows XP SP2
    >> - MS Access 2003
    >>
    >> I have a table called tblSemester with the following information:
    >> Field names: Semester, semStartDate, semEndDate
    >> Sample data: S10506,01/10/2005,27/01/2006
    >> S20506,28/01/2006,09/06/2006
    >> Summer0506,10/06/2006,30/09/2006
    >> S10607,01/10/2006,29/01/2007
    >> S20607,30/01/07,02/06/2007
    >>
    >> I have the following function:
    >> ************************
    >> Public Function setSemester() As String
    >> Dim varCriteria As String
    >> varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
    >> [semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
    >> varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
    >> setSemester = varSemester
    >> End Function
    >> ***************************
    >>
    >> And finally, I have a form with one control, txtSemester, where the

    > Default
    >> value is set to "setSemester"
    >>
    >> My problem is:
    >> the result of the function is always wrong as it assumes the date is in

    > the
    >> American format.
    >> For example, if today's date is 09/05/2006 (9 May 2006), the function
    >> will
    >> return "Summer0506"(as would be expected if today's date were

    > 05/Sept/2006),
    >> instead of "S20506" as it should.
    >> Could you please let me know where else in the system I should set the
    >> location to UK, or any other way to work around this problem.
    >>
    >> Let me know if you need other details.
    >>
    >> Many many thanks,
    >>
    >> Elaine
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >
    >
     
  6. Elaine

    Elaine
    Expand Collapse
    Guest

    Hi Ken,

    many thanks for this. I shall start using your Usdate function from now on
    Cheers
    Elaine
    "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    news:646DD3FE-1C9C-453F-8CD0-8347CDE37E07@microsoft.com...
    > Elaine:
    >
    > Date literals in Access must be in US or an otherwise internationally
    > unambiguous format. While dates are stored as a 64 bit floating point
    > number
    > regardless of the local system date format, they are expressed by default
    > in
    > the local short date format. Change your function to the following and it
    > should be fine:
    >
    > ************************
    > Public Function setSemester() As String
    >
    > Dim varCriteria As String
    > varCriteria = "semStart <= #" & Format(Date, "mm/dd/yyyy") & _
    > "# AND semEnd >= #" & Format(Date, "mm/dd/yyyy") & "#"
    >
    > setSemester = DLookup("Semester", "tblSemester", varCriteria)
    >
    > End Function
    > ***************************
    >
    > As this is a common requirement I have the following function in a
    > standard
    > module:
    >
    > Function USDate(varDate)
    >
    > If Not IsNull(varDate) Then
    > USDate = "#" & Format(varDate, "mm/dd/yyyy") & "#"
    > End If
    >
    > End Function
    >
    > It can then be called when necessary, e.g. in your case:
    >
    > varCriteria = "semStart <= " & USDate(Date) " & _
    > "AND semEnd >= " & USDate(Date)
    >
    > Ken Sheridan
    > Stafford, England
    >
    > "Elaine" wrote:
    >
    >> Hi guys,
    >> I'm having some problems with date formatting.
    >>
    >> Background:
    >> - My system regional settings is set to UK system
    >> - Location is set to United Kingdom
    >> - All my dates in my Access tables are formatted as "dd/mm/yyyy"
    >> - MS Windows XP SP2
    >> - MS Access 2003
    >>
    >> I have a table called tblSemester with the following information:
    >> Field names: Semester, semStartDate, semEndDate
    >> Sample data: S10506,01/10/2005,27/01/2006
    >> S20506,28/01/2006,09/06/2006
    >> Summer0506,10/06/2006,30/09/2006
    >> S10607,01/10/2006,29/01/2007
    >> S20607,30/01/07,02/06/2007
    >>
    >> I have the following function:
    >> ************************
    >> Public Function setSemester() As String
    >> Dim varCriteria As String
    >> varCriteria = "[semStart] <= #" & Format(Date, "dd/mm/yyyy") & "# AND
    >> [semEnd] >= #" & Format(Date, "dd/mm/yyyy") & "#"
    >> varSemester = DLookup("[Semester]", "tblSemester", varCriteria)
    >> setSemester = varSemester
    >> End Function
    >> ***************************
    >>
    >> And finally, I have a form with one control, txtSemester, where the
    >> Default
    >> value is set to "setSemester"
    >>
    >> My problem is:
    >> the result of the function is always wrong as it assumes the date is in
    >> the
    >> American format.
    >> For example, if today's date is 09/05/2006 (9 May 2006), the function
    >> will
    >> return "Summer0506"(as would be expected if today's date were
    >> 05/Sept/2006),
    >> instead of "S20506" as it should.
    >> Could you please let me know where else in the system I should set the
    >> location to UK, or any other way to work around this problem.
    >>
    >> Let me know if you need other details.
    >>
    >> Many many thanks,
    >>
    >> Elaine
    >>

    >
     

Share This Page