Date range on report not working (XP/2000)

Topic

New Reply

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.

Reply | Quote

Replies

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]=#” & strFromDate & “#) And ([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.

Reply | Quote

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.

Reply | Quote

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]=#” & strFromDateSQL & “#) And ([DueDate]<=#" & strToDateSQL & "#)"
…

Reply | Quote

Added: you can also use

strWhere = “[DueDate] Between #” & strFromDateSQL & “# And #” & strToDateSQL & “#”

Reply | Quote

Thanks for your help and explanation.

Everything seems to be working properly now.

Reply | Quote