Form with Multiple Parameters

A

ATSBC03

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
 
M

Marshall Barton

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


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
 
A

ATSBC03

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top