More than one where condition

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am attempting to open a report with more than one where condition
and I am getting a Type Mismatch error. I can't figure out quite how
to get this to work.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim stDocName As String
Dim stWhere As String

If Len([cboFilter1] & "") <> 0 And Len([cboSearchReportsTo] & "") = 0
_
And Len([cboZone] & "") <> 0 And _
Len([cboSearchTM] & "") = 0 And Len([cboSearchDept] & "") <> 0 Then

stWhere = ("ZoneID =" & cboZone) And ("DeptID =" &
cboSearchDept)
DoCmd.OpenReport "rptByShop", acPreview, , stWhere

End If

First of all, can I filter a report through a form with more than one
where condition?

The user selects "conditions" from a drop down form and the report
opens based on their selections. I am trying to get the report to
open based on more than one where condition and cannot figure
out if this can be accomplished. Any assistance will be appreciated.
 
Opal said:
I am attempting to open a report with more than one where condition
and I am getting a Type Mismatch error. I can't figure out quite how
to get this to work.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim stDocName As String
Dim stWhere As String

If Len([cboFilter1] & "") <> 0 And Len([cboSearchReportsTo] & "") = 0
_
And Len([cboZone] & "") <> 0 And _
Len([cboSearchTM] & "") = 0 And Len([cboSearchDept] & "") <> 0 Then

stWhere = ("ZoneID =" & cboZone) And ("DeptID =" &
cboSearchDept)
DoCmd.OpenReport "rptByShop", acPreview, , stWhere

End If

First of all, can I filter a report through a form with more than one
where condition?
Click to expand...


This kind of thing is usually handled with this kind of
logic:

If Len(cboFilter1 & "") <> 0 Then
stWhere = stWhere & " And somefield = " & cboFilter1
End If
If Len(cboSearchReportsTo & "") <> 0 Then
stWhere = stWhere & "And tofield = " & cboSearchReportsTo
End If
If Len([cboZone] & "") <> 0 Then
stWhere = stWhere & " And ZoneID = " & cboZone
End If
If Len([cboSearchTM] & "") <> 0 Then
stWhere = stWhere & " And TMfield = " & cboSearchTM
End If
If Len([cboSearchDept] & "") <> 0 Then
stWhere = stWhere & " And DeptID = " & cboSearchDept
End If

DoCmd.OpenReport "rptByShop", acPreview, , Mid(stWhere, 6)

BUT that only works for number type fields. If a field is a
Text field, then use:

stWhere = stWhere & " And textfield = """ & cboxxx & """"

And for a date fields:
stWhere = stWhere & " And datefield = #" & cboyyy & "#"
 
Opal said:
I am attempting to open a report with more than one where condition
and I am getting a Type Mismatch error.  I can't figure out quite how
to get this to work.
Click to expand...
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Click to expand...
   Dim stDocName As String
   Dim stWhere As String
Click to expand...
If Len([cboFilter1] & "") <> 0 And Len([cboSearchReportsTo] & "") = 0
_
And Len([cboZone] & "") <> 0 And _
Len([cboSearchTM] & "") = 0 And Len([cboSearchDept] & "") <> 0 Then
Click to expand...
       stWhere = ("ZoneID =" & cboZone) And ("DeptID =" &
cboSearchDept)
       DoCmd.OpenReport "rptByShop", acPreview, , stWhere
Click to expand...
   End If
Click to expand...
First of all, can I filter a report through a form with more than one
where condition?
Click to expand...

This kind of thing is usually handled with this kind of
logic:

If Len(cboFilter1 & "") <> 0 Then
        stWhere = stWhere & " And somefield = " & cboFilter1
End If
If Len(cboSearchReportsTo & "") <> 0 Then
        stWhere = stWhere & "And tofield = " & cboSearchReportsTo
End If
If Len([cboZone] & "") <> 0 Then
        stWhere = stWhere & " And ZoneID = " & cboZone
End If
If Len([cboSearchTM] & "") <> 0 Then
        stWhere = stWhere & " And TMfield = " & cboSearchTM
End If
If Len([cboSearchDept] & "") <> 0 Then
        stWhere = stWhere & " And DeptID = " & cboSearchDept
End If

DoCmd.OpenReport "rptByShop", acPreview, , Mid(stWhere, 6)

BUT that only works for number type fields.  If a field is a
Text field, then use:

        stWhere = stWhere & " And textfield = """ & cboxxx & """"

And for a date fields:
        stWhere = stWhere & " And datefield = #" & cboyyy & "#"

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you, Marsh, that helps alot! They are number fields.
 
Back
Top