create pdf from filtered report in access using vba

cninghm
cninghm used Ask the Experts™
on
Hi, I found the answer to my question on EE, but when I tried it, it doesn't work.   Maybe it's because I'm using Access 2010 and the answer was for Access 2007?  
The question:
Using VBA, how do I create a PDF from a report and send it a filter.

The Code I'm using:
        Dim strReport As String
        Dim VarGroup As String
        VarGroup = "CMD20032"
        strReport = "rpt_PB1"
        DoCmd.OpenReport strReport, acViewPreview, , "[Group Number]=" & VarGroup, acHidden
        DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, filename, False
        DoCmd.Close acReport, strReport
The Problem:
It prompts me to enter a parameter with "CMD20032" as the Parameter name.
What am I doing wrong?  The report has the field "Group Number" on it.
Betty
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Try:

Dim strCriteria as string

strCriteria  = "[Group Number] = " & chr$(34) & varGroup & chr$(34)

DoCmd.OpenReport strReport, acViewPreview, , strCriteria, acHidden

Author

Commented:
Woah Dude! You rock. Thanks.  
Don't you wish this was $$ instead of points.  If we could only make money that quickly :)
Thanks,
Betty

Author

Commented:
Thanks again.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Betty,

The explanation of what was happening was that the criteria you were passing to the report was:

"[Group Number] = CMD20032"

But since CMD20032 was not wrapped in quotes, JET interpreted this as a parameter rather than a value, so when the field you are trying to filter on is a string, you need to make sure you wrap the value in quotes.  The other common way to do that is:

 DoCmd.OpenReport strReport, acViewPreview, , "[Group Number]= '" & VarGroup & "'", acHidden
 
Where I have inserted a single quote just before the double quote after the =, and have added a single quote surrounded by double quotes at the end of the string.  Personally, I find both of these as tedious, hard to type and harder to read, so I have a function that I use.  The syntax for using it would look like:

DoCmd.OpenReport strReport, acViewPreview, , "[Group Number]= " & Quotes(VarGroup), acHidden

Public Function Quotes(QuoteWhat as Variant, Optional QuoteWith as string = """") as string

    Quotes = quotewith & replace(NZ(QuoteWhat, ""), QuoteWith, QuoteWith & QuoteWith) & QuoteWith

End Function

Author

Commented:
Thanks! I had tried various combinations of double and single quotes and obviously have to agree with you that they are tedious, especially since I neglected to get it right. Thanks for the explanation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial