Form with Multiple Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
ATSBC03 said:
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
Click to expand...


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
 
Thank you very much, that worked perfectly.

Marshall Barton said:
ATSBC03 said:
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
Click to expand...


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
Click to expand...
 
Back
Top