Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range on report not working (XP/2000)

    In your WhereCondition (strwhere), you'll have to use USA date format mm/dd/yyyy. So you'll have to handle the strings to be used in strWhere separately from the strings used for display.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date range on report not working (XP/2000)

    I'm using a form to try to set a date range on a report but it isn't working correctly

    This is the code that opens the report

    strFromDate = Format(txtFromDate, "dd/mm/yyyy")
    strToDate = Format(txtToDate, "dd/mm/yyyy")

    strDocName = "rptReExamSchedule"

    If Me.chkIncludeBefore = True Then
    strWhere = "[DueDate]<= #" & strToDate & "#"
    'Debug.Print strWhere
    'DueDate Between #1/1/2007# And #12/1/2007#
    gstrExamPeriod = "before " & strToDate
    Else
    strWhere = "([DueDate]>=#" & strFromDate & "#) And ([DueDate]<=#" & strToDate & "#)"
    'Debug.Print strWhere
    'DueDate Between #1/1/2007# And #12/1/2007#
    gstrExamPeriod = "between " & strFromDate & " and " & strToDate
    End If

    DoCmd.OpenReport strDocName, acViewPreview, , strWhere

    An example where clause would be this
    ([DueDate]>=#01/09/2007#) And ([DueDate]<=#01/08/2008#)

    Unfortunately I'm getting results for records with a DueDate of 05/04/2007

    I'm in the UK and use the date format dd/mm/yyyy. I suspect this is something to do with the date comparison assuming American date formats.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date range on report not working (XP/2000)

    So I need a second pair of 'strDate' variables formatted to mm/dd/yyyy for use in the where clause?

    DueDate is a calcuated field in the report's query. Do I need to re-format this to US too? I get pretty confused with dates, particularly the effects (or not of formatting) the 'real' date.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range on report not working (XP/2000)

    You don't need to do anything with DueDate itself - it's a (calculated) date field, represented internally as a number (the number of days since 30 December 1899).

    But when you use a WhereCondition argument for DoCmd.OpenForm or DoCmd.OpenReport, you're passing a text string that will be processed by the SQL engine, and SQL is strictly US oriented. So if your WhereCondition string is

    "[DueDate] <= #01/09/2007#"

    this is interpreted as "on or before January 9, 2007"

    Declare two new variables:

    Dim strFromDateSQL As String
    strToDateSQL As String

    strFromDateSQL = Format(txtFromDate, "mm/dd/yyyy")
    strToDateSQL = Format(txtToDate, "mm/dd/yyyy")

    ...
    strWhere = "[DueDate]<= #" & strToDateSQL & "#"
    ...
    strWhere = "([DueDate]>=#" & strFromDateSQL & "#) And ([DueDate]<=#" & strToDateSQL & "#)"
    ...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range on report not working (XP/2000)

    Added: you can also use

    strWhere = "[DueDate] Between #" & strFromDateSQL & "# And #" & strToDateSQL & "#"

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date range on report not working (XP/2000)

    Thanks for your help and explanation.

    Everything seems to be working properly now.

Posting Permissions

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