Filter Using a Date

[jdgreen]

I have a very simple database that is a schedule. The key field is the start date of the schedule. I'm trying to open a report with a filter as follows:
Dim stDocName As String
Dim lookupfilter As String

lookupfilter = "[ControlDate]=" & Me![ControlDate]
stDocName = "rptDays"
DoCmd.OpenReport stDocName, acPreview, , lookupfilter

I've used this same format on many other applications and never had a problem. Does it have something to do with it being a date? Am I being dense because it's too late?

John Green

 

[dhookom]

It would help if you stated a question in there somewhere. I can only assume you aren't getting any data in the report. If [ControlDate] has a time element then you would need to remove the time element or expand the date range. You might also try:
lookupfilter = "[ControlDate]=#" & Me![ControlDate] & "#"


Duane
MS Access MVP

 

[jdgreen]

It was too late. I didn't set the default format to just record the date in the table so the time was being recorded so the filter didn't find anything that matched. I was getting just a blank report. Thanks.

John Green

 

[dhookom]

Then, you could use
lookupfilter = "DateValue([ControlDate])=DateValue(#" & Me![ControlDate] & "#)"

Duane
MS Access MVP