filtering while opening a report

  • Thread starter Thread starter Ves G
  • Start date Start date
V

Ves G

I'm trying to open a report of records with a certain
date. The problem is that the date is formatted with the
Date and Time. I use a form to get my initial filter
variables and then use these variables to build a WHERE
condition statement using the LIKE function. I'm using the
following code to open the report with the selected
records but it still gives me all the records:

MyFilterDate = Forms!GetReportVariables.txtReceiptDate & "
##:##:## ??"
strCriteria = "Reports!receipts.[Date] like '" &
MyFilterDate & "'"
DoCmd.OpenReport "receipts", acviewPreview, ,strCriteria

Any help would be appreciated.

Thanks,

VesG
 
You probably don't want to use "Like" with a date type value. Also, your
strCriteria should omit the "Reports!receipts." and just use the field name
ie:
strCriteria = "[Date] =#" & MyFilterDate & "#"
 
I'm trying to open a report of records with a certain
date. The problem is that the date is formatted with the
Date and Time. I use a form to get my initial filter
variables and then use these variables to build a WHERE
condition statement using the LIKE function. I'm using the
following code to open the report with the selected
records but it still gives me all the records:

MyFilterDate = Forms!GetReportVariables.txtReceiptDate & "
##:##:## ??"
strCriteria = "Reports!receipts.[Date] like '" &
MyFilterDate & "'"
DoCmd.OpenReport "receipts", acViewPreview, ,strCriteria
Click to expand...

Try:

'***
Dim varFilterDate As Variant
varFilterDate = DateValue(Forms!GetReportVariables.txtReceiptDate)
strCriteria = "DateValue([Date]) = " & varFilterDate
DoCmd.OpenReport "receipts", acviewPreview, ,strCriteria
'***
 
That worked.

Thanks very much.

Ves Garrett
-----Original Message-----
I'm trying to open a report of records with a certain
date. The problem is that the date is formatted with the
Date and Time. I use a form to get my initial filter
variables and then use these variables to build a WHERE
condition statement using the LIKE function. I'm using the
following code to open the report with the selected
records but it still gives me all the records:

MyFilterDate = Forms!GetReportVariables.txtReceiptDate & "
##:##:## ??"
strCriteria = "Reports!receipts.[Date] like '" &
MyFilterDate & "'"
DoCmd.OpenReport "receipts", acViewPreview, ,strCriteria
Click to expand...

Try:

'***
Dim varFilterDate As Variant
varFilterDate = DateValue(Forms! GetReportVariables.txtReceiptDate)
strCriteria = "DateValue([Date]) = " & varFilterDate
DoCmd.OpenReport "receipts", acviewPreview, ,strCriteria
'***

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<



.
Click to expand...
 
Back
Top