Dates for Reports

[maud]

I have a form that generates several reports. One of the options I
have is to generate a report based on date between two dates. I have
two text boxes on the form to specify these dates and a button to
generate the report with the following code:

strWhere = "([DueDate] between Forms![frmReports]!BeforeD and Forms!
[frmReports]!AfterD )"
DoCmd.OpenReport "rptAll", acViewPreview, , strWhere

However this always generates an empty data set regardless of the
dates I use. For example using BeforeD "1/1/2010" and AfterD
"1/1/2000" (or switching these). Any ideas?

thanks!

 

[Ken Snell \(MVP\)]

Try concatenating the actual date values into the string:

strWhere = "[DueDate] between " & _
Format(Forms![frmReports]!AfterD, "\#mm\/dd\/yyyy\#") _
& " And " & Format(Forms![frmReports]!BeforeD, "\#mm\/dd\/yyyy\#")

 

[Ken Sheridan]

Concatenate the values of the controls into the string expression as date
literals, formatting them in an internationally unambiguous format like so:

Dim strWhere As String

strWhere = _
"[DueDate] Between #" & _
Format(Forms![frmReports]!BeforeD,"yyyy-mm-dd") & _
"# And #" & _
Format(Forms![frmReports]!AfterD,"yyyy-mm-dd") & "#"

DoCmd.OpenReport "rptAll", _
View:=acViewPreview, _
WhereCondition:=strWhere

What may be happening at present is that the values entered in the controls
are, if in short date format, being interpreted as arithmetical expressions.
Because of the way the date/time data type is implemented in Access as a 64
bit floating point number a value of 10/11/2000 for instance would as an
arithmetical expression evaluate to 0.000454545454545455, which is the
underlying value of 30 December 1899 00:00:39, the origin for Access's
date/time implementation being 30 December 1899 00:00:00. Now, I don't
imagine you are going to have any dates around then in your table!.

I'm also wondering whether you have the BeforeD and AfterD controls the
wrong way round in the expression. To my mind AfterD would logically be the
start date and BeforeD the end date, i.e. dates after the former and before
the latter should be returned.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

I believe you need to delimit date values with the "#" symbol ... e.g.,
Between #1/1/2000# And #1/1/2010#

Regards

Jeff Boyce
Microsoft Office/Access MVP