Docmd.openreport Where Clause syntax

1
votes

I am trying to save individual records in a report to PDF files. using Access 2010. I got this to work, but I need to put a start date and end date in, and I can't seem to figure out the syntax. Here is what I have so far:

DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]=" & temp & _ And [Service Date] Between & begindate And  enddate;

The top line works fine but when I add the second I can't get it to work. I have tried all the qualifiers', ", and # and many different variations of syntax with no luck.

begindate and enddate are strings that I capture from an input box. Should I change these to a date? I have done a lot of reading on here and based on suggestions I thought about putting a text box on the form for begin and end but I think I would rather do the inputbox.

1
sqlvbams-access-2010

1 Answers

5
votes

Are you sure the syntax you posted above is accurate, because it is not correct.

  1. I am assuming 'temp' is numeric, else you need to enclose in single-quotes;
  2. Tthe continuation line is missing the leading " followed by a space;
  3. Missing quotes, spaces around your BETWEEN and AND

You can get away with strings for the dates as long as you did something like:

begindate = #1/1/2005#
enddate = #1/1/2012#

Here is what I believe your syntax should look like (I tested and it works):

DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]=" & temp & _
    " And [Service Date] Between #" & begindate & "# And #" & enddate & "#"