Form with Multiple Parameters

[Guest]

I have a Report bound to a query that upon opening opens an unbound form with
4 combo boxes and 2 date input fields. The choices selected (or not selected)
filter the report through the Where Condition string. I had no problem with
the first combo box (cboSite) but the second combo box (cboDept) could be in
3 different columns in the table so my where condition needs to include an
or. However, I can't seem to figure out how to include it so that it will
still filter under the first combo box. Any help will be appreciated. Code
from Click Event is pasted below.

Private Sub cmdOK_Click()

Dim strDocName As String
Dim strWhere As String

strWhere = "1=1"
stDocName = "OccurrenceReportSpecSiteDept"

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND [Dept/Specialty] = """ & _
Me.cboDept & """"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " OR [Dept/Specialty2] = """ & _
Me.cboDept & """"
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

Debug.Print strWhere

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

DoCmd.Close acForm, "frmSiteDept"

End Sub

 

[Marshall Barton]

I have a Report bound to a query that upon opening opens an unbound form with
4 combo boxes and 2 date input fields. The choices selected (or not selected)
filter the report through the Where Condition string. I had no problem with
the first combo box (cboSite) but the second combo box (cboDept) could be in
3 different columns in the table so my where condition needs to include an
or. However, I can't seem to figure out how to include it so that it will
still filter under the first combo box. Any help will be appreciated. Code
from Click Event is pasted below.

strWhere = "1=1"
stDocName = "OccurrenceReportSpecSiteDept"

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND [Dept/Specialty] = """ & _
Me.cboDept & """"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " OR [Dept/Specialty2] = """ & _
Me.cboDept & """"
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

You need parenthesis around the OR expressions:

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept/Specialty] = """ & _
Me.cboDept & """ " OR [Dept/Specialty2] = """ & _
Me.cboDept & """ ) "
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

 

[Guest]

Thank you very much, that worked perfectly.

I have a Report bound to a query that upon opening opens an unbound form with
4 combo boxes and 2 date input fields. The choices selected (or not selected)
filter the report through the Where Condition string. I had no problem with
the first combo box (cboSite) but the second combo box (cboDept) could be in
3 different columns in the table so my where condition needs to include an
or. However, I can't seem to figure out how to include it so that it will
still filter under the first combo box. Any help will be appreciated. Code
from Click Event is pasted below.

strWhere = "1=1"
stDocName = "OccurrenceReportSpecSiteDept"

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND [Dept/Specialty] = """ & _
Me.cboDept & """"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " OR [Dept/Specialty2] = """ & _
Me.cboDept & """"
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

You need parenthesis around the OR expressions:

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept/Specialty] = """ & _
Me.cboDept & """ " OR [Dept/Specialty2] = """ & _
Me.cboDept & """ ) "
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If