Open report with Where Condition

[chuckcoleman]

I can't get a Macro to run correctly. I have an Access 2010 report that I want to run when someone clicks on a Command button. The on-click of the button runs an embeded Macro. I have a Where Condition in the Macro that says:

=[Reports]![Open Work Order Report]![RequestedDateX]< & Date()

The macro opens the report but there aren't any records. [RequestedDateX] is a Date field in the report. I want to disply all records where the requested date is less than the day the Command button is clicked, i.e. the requested date has passed.

I don't want to modify the reports underlying query because the same report is used elsewhere and therefore I would need to maintain two query's. Seems like I should be able to do this, right?

 

[pbaldy]

I don't use macros. This would be the syntax in code:

http://www.baldyweb.com/wherecondition.htm

You can try just using the field name in the macro:

[RequestedDateX]< Date()

 

[chuckcoleman]

Still can't get it. It's essentially ignoring the Where clause. I followed the Badly Web example, (in the Macro), and have:

"[RequestedDateX]<#" & Date() & "#"

It acts like the above line isn't even in the Where statement of the Macro.

Any other ideas?

 

[pbaldy]

Have you tried VBA instead of a macro? I think the syntax is a little different, but as I said I don't use macros.

 

[chuckcoleman]

I did, it didn't work. Same behavior - ignores that I'm looking for dates < today.

DoCmd.OpenReport "Open Work Order Report", acViewPreview, "RequestedDate < #" & Date & "#"

Chuck

 

[pbaldy]

I believe you have it in the wrong position. Try

DoCmd.OpenReport "Open Work Order Report", acViewPreview, , "RequestedDate < #" & Date & "#"

 

[chuckcoleman]

You're my HERO! That was it. Thank you for sticking with me!!

Chuck

 

[pbaldy]

No problem Chuck, glad we got it sorted out.

 

[Abouya]

I believe you have it in the wrong position. Try

DoCmd.OpenReport "Open Work Order Report", acViewPreview, , "RequestedDate < #" & Date & "#"

Is there a way to change the acViewPreview with another syntax to open the report in normal mode, not preview mode. i tried many alternative and most of them send the report directly to the printer.

 

[pbaldy]

Did you look in help, or look at the options presented while typing in the code? Preview is most common, but if you want report view I'm sure there's an option for it. If I had to guess, acViewReport but I'm not on a computer right now.