Open report and filter in VBA

[brocky]

Hi,<br>
I'm trying to open a report in VBA and am having trouble with the filtering syntax. I appear to be opening the report ok, but I'm seeing parameter dialog boxes with the filter criteria that I'm trying to specify as the prompt. Code is as follows:<br>
<br>
Private Sub Label20_Click()<br>
Dim stCriteria, BatchNo As String<br>
BatchNo = Me![Batch Number]<br>
stCriteria = "[Batch Number] =" & BatchNo<br>
DoCmd.OpenReport "Certificate of Analysis", acViewPreview, , stCriteria<br>
End Sub<br>
<br>
How do I fix this?<br>
<br>
Chris<br>
Melbourne

 

[smedvid]

Make sure report form has Filter On = Yes.<br>
Try hard coding an example into the Filter section of the report. I usally use just the field name less the brackets. ie. BatchNumber='12345'. Note: I do not use spaces in my field names. Another approach is to put the full filter string including the table name. ie. SALES![BatchNumber]='12345'<br>
<br>
I once spent over 2 hours looking for a bug in a filter. The variable name was misspelled, with an extra space. ie. Batch^^Number instead of Batch^Number. This is one good reason not to use spaces. <br>
<br>
htwh,<br>
Steve

 

[PSchiele]

If your code is written exactly as you have it listed in your message, I believe all you did was forget to wrap single quotes around your variable. You will need to do this since BatchNo is of type string. If it should be a number, change your declaration statement or if it is indeed a string, try this line instead:<br>
<br>
stCriteria = "[Batch Number] = '" & BatchNo & "'"<br>
<br>
Instead of giving a type mismatch error, Access pops up a parameter box.<br>
<br>

 

[brocky]

Yes, that works. It took me some time to work out that the syntax. I've worked in some other programming environments, and the use of the single quote (') as both an important part of the program code and as the signal for commenting is a strange choice. Who am I to argue with Microsoft?<br>
<br>
Chris<br>
Melbourne