Using a variable in criteria

[buratti]

I have current code that first updates a field named Exported in a table to the current time (using the Now() function), then it opens a report based on that table with criteria of Now() for that same Exported field. My current code is:

DoCmd.RunSQL "UPDATE [Orders] SET [Orders].[Exported] = Now() WHERE ((([Orders].[Status ID])=2));"
DoCmd.OpenReport "Card Charges and Updates", acViewPreview, "", "Exported = Now()", acNormal

Sometimes everything is fine and others the report is blank. I'm assuming that it could be due to a hang in the computer making a different time being generated by the Now() function used.

Is there any way to use a variable in the criteria portion in the code to make it something like?


Dim ExportTime As Date
ExportTime = Now()

DoCmd.RunSQL "UPDATE [Orders] SET [Orders].[Exported] = ExportTime WHERE ((([Orders].[Status ID])=2));"
DoCmd.OpenReport "Card Charges and Updates", acViewPreview, "", "Exported = ExportTime", acNormal

I've tried this exact code, but it throws up errors. Is it even possible to use variables like so, and if so what would be the correct syntax? If not, does anyone have any suggetions on how to make this work?
Thanks

 

[boblarson]

The problem is you are not keeping the functions and variables OUTSIDE of the quotes (and for date/time, you add # delimiters)

DoCmd.RunSQL "UPDATE [Orders] SET [Orders].[Exported] =#" & Now() & "# WHERE ((([Orders].[Status ID])=2));"
DoCmd.OpenReport "Card Charges and Updates", acViewPreview, "", "Exported =#" & Now() & "#", acNormal

 

[buratti]

Ah thanks works great!!! I never really learned much about vba code, and what I do know is stuff I just taught myself. I can read most of it, but when it comes time to write it myself, I cant get the syntax correct. i would of never of figured out this one on my own. Thanks again.