Filter Report by Form using combo boxes

[TubbzUK]

Hi,

I have report that i want to filter by various different criteria.
Say for example there is 5 different fields. If you select option 1 of filter 1 then click apply the report will only show all records that contain option1.
If option 1 is blank and you select something in option 2, clcik apply and only option 2 will be shown. also if you select option 1 and option 2 click apply and only theose records matching both will be shown.

I have searche dand found some code to help me with this.
When i alter it to suit my database i get a "Enter Parameter" box appear and the report is blank with no records in view.
I have filtered all of my combo boxes to display options taken from the database by the row source being a sql string SELECT DISTINCT [QC tasting].[Sample nature?] FROM [QC tasting];

The code i have got on my apply button is

Private Sub Apply_Click()
Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![QC tasting].Filter = strSQL
Reports![QC tasting].FilterOn = True
End If
End Sub

ANy help would be great,

regards,



Alan

[alansidman]

What field is the parameter looking for: Is there a typo somewhere such that Access is looking for a non-existing field. That is usually the cause of these situations. Does your query work if you take the sql statement to the QBE.

BTW:

If I were doing this, I would build the report off a query and use a form to generate the query and the report. Here are two examples of what I mean.

http://www.datapigtechnologies.com/f...earchform.html

and

http://www.datapigtechnologies.com/f...mtoreport.html

[TubbzUK]

HI, thanks for your help,
i am a complete novice though and do not get the sql statement to QBE bit.

what do i need to do to do that?

regards,

Alan

[alansidman]

You have built a query in VBA. All I am suggesting is create that same query using the same criteria and build it in the Query Builder and see if it works.

Alan