Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Location
    New Zealand
    Posts
    10

    Unanswered: Date Format US or not

    I am trying to use the code from Allen Browne where Dates are handled whether or not in dd/mm/yy or mm/dd/yy

    to open a report and list entries where a date field is less than 2 months away
    I have
    DoCmd.OpenReport "rptCerts", acViewReport, , "[CertDateExp] < #" & DateAdd("m", 2, Date) & "#"
    which is fine but not in this part of the world as today being 2/3 becomes 2/5 in two months which 5th Feb elsewhere

    code below is me just mucking about to no useful effect
    Private Sub btnCertsExp_Click()
    Dim strWhere As String
    Dim strDateField As String
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    'Set Date for all before two months from now
    strDateField = CertDateExp
    strWhere = "(" & strDateField & " >= " & Format("#" & DateAdd("m", 2, Date) & "#", strcJetDate) & ")"


    DoCmd.OpenReport "rptCerts", acViewReport, , strWhere
    End Sub
    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Have you stepped through the code to see what values are being passed to and from the function?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Access is smarter than that and functions such as DateAdd() are aware of the local date format. In the country where I live, the date format is "d/mm/yyyy".

    When I type: ? date in the immediate window, I get: 2/03/2013
    When I type: ? "#" & DateAdd("m", 2, Date) & "#" I get: #2/05/2013#
    Have a nice day!

  4. #4
    Join Date
    Feb 2012
    Location
    New Zealand
    Posts
    10
    When I type: ? date in the immediate window, I get: 3/03/2013
    When I type: ? "#" & DateAdd("m", 2, Date) & "#" I get: #3/05/2013#
    with the existing code (as above the first line after "I Have")
    when i open the report the Filter the filter States
    [CertDateExp]<#3/05/2013# which sort of looks like what i want except that date is 5th March which is not

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I answered faster than I should have, sorry!

    When a date is passed to an Access query, it must in the format "mm/dd/yyyy" or "yyyy-mm-dd" (ISO).
    Have a nice day!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    play safe and use the ISO format.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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