Multiple criteria in a SQL query

[Huddle]

I need some direction on where to start.



I have a form "Fm_Criteria_Parameter". I have a report "Rpt_Criteria" that I have a SQL query in the Record Source.

I want the user to put in as much or little criteria on the form that is needed to get their data.

I understand the formula Form!Fm_Criteria!Combox...will select the parameter for whichever combo box has information in it.

I also understand that I can not put the formula for each combo box on the "Criteria" line in the query because all criteria would have to be met including the null fields.

Example: The user enters data in Field 1, 6 & 7 but leaves 2,3,4 & 5 blank. I want to query the data to bring up only the records that meet the criteria entered.

Can you push me in the right direction on how to accomplish this?

[orange]

Is this the sort of thing you're looking for?
http://www.fontstuff.com/access/acctut19.htm

There are other tutorials at the site

[Huddle]

Using the examples in the tutorial I've have the below created. My problem is the code for applying the filter to the report and switching it on. My ultimate goal is to have one "Parameter Criteria Form" to serve any of the reports I want to use therefore putting in a code with a report's name will only allow that form to be use for that report. Am I correct in this thinking? How can I apply the form to multiple reports?

Private Sub CmdFilter_Click()
Dim strBldgNum As String
Dim strFSL As String
Dim strCommander As String
Dim strInspector As String
Dim strDistrict As String
Dim strFY As String
Dim strState As String
Dim strCity As String
Dim strFilter As String


If IsNull(Me.BldgNumCmb.Value) Then
strBldgNum = "Like '*'"
Else
strBldgNum = "='" & Me.BldgNumCmb.Value & "'"
End If
If IsNull(Me.FSLCmb.Value) Then
strFSL = "Like '*'"
Else
strFSL = "='" & Me.FSLCmb.Value & "'"
End If
If IsNull(Me.CommanderCmb.Value) Then
strCommander = "Like '*'"
Else
strCommander = "='" & Me.CommanderCmb.Value & "'"
End If
If IsNull(Me.InspectorCmb.Value) Then
strInspector = "Like '*'"
Else
strInspector = "='" & Me.InspectorCmb.Value & "'"
End If
If IsNull(Me.DistrictCmb.Value) Then
strDistrict = "Like '*'"
Else
strDistrict = "='" & Me.DistrictCmb.Value & "'"
End If
If IsNull(Me.FYCmb.Value) Then
strFY = "Like '*'"
Else
strFY = "='" & Me.FYCmb.Value & "'"
End If
If IsNull(Me.StateCmb.Value) Then
strState = "Like '*'"
Else
strState = "='" & Me.StateCmb.Value & "'"
End If
If IsNull(Me.CityCmb.Value) Then
strCity = "Like '*'"
Else
strCity = "='" & Me.CityCmb.Value & "'"
End If
strFilter = "[BldgNum] " & strBldgNum & " AND [FSL] " & strFSL & " AND [Commander] " & strCommander & " AND [Inspector] " & strInspector & " AND [District] " & strDistrict & " AND [FY] " & strFY & " AND [State] " & strState & " AND [City] " & strCity

On Error GoTo Err_CmdFilter_Click

Screen.PreviousControl.SetFocus
DoCmd.FindNext
Exit_CmdFilter_Click:
Exit Sub
Err_CmdFilter_Click:
MsgBox Err.Description
Resume Exit_CmdFilter_Click

End Sub