Filter a Report from a Pop-Up Form

[Guest]

In the Microsoft example, How to Filter a Report from a Pop-Up Form, I'm
trying to filter different data types. The example works great for text
values, but when I try to use it on numerical values or date values I get the
following error message:

Data type mismatch in criteria expression


Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![WorkorderFilterReport].Filter = strSQL
Reports![WorkorderFilterReport].FilterOn = True
Else
Reports![WorkorderFilterReport].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub

Can this be altered to accept any data type? Or, is the problem with the
configuration of each combo-box?

Thx.

 

[Douglas J. Steele]

For numeric values, remove the two "Chr(34) &" in strSQL.

For date values, you need to delimit them with # characters (instead of
Chr(34), which is a double quote). As well, you need to ensure that the date
is in a format that Access will accept. Since you have no control over what
Short Date format your users may have chosen in Regional Settings, that
means it's a good idea to explicitly format the dates so that they'll be
recognized:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Format(Me("Filter" & intCounter), "\#yyyy\-mm\-dd\#") & " And "

Now, how you're going to determine when it's text, when it's a number and
when it's a date is more of an issue.

One approach would be to concatenate the data type in the Tag, along with
the Field name. If you've currently got Field1 as the Tag property, you
could use Field1;Text or Field1;Number or Field1;Date instead. You'd then
change your code to something like:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
Dim varTag As Variant

'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
varTag = Split(Me("Filter" & intCounter).Tag, ";")
Select Case varTag(1)
Case Date
strSQL = strSQL & "[" & varTag(0) & "] = " & _
Format(Me("Filter" & intCounter), "\#yyyy\-mm\-dd\#") &"
And "
Case Number
strSQL = strSQL & "[" & varTag(0) & "] = " & _
Me("Filter" & intCounter) & " And "
Case Text
strSQL = strSQL & "[" & varTag(0) & "] = " & _
Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End Select
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![WorkorderFilterReport].Filter = strSQL
Reports![WorkorderFilterReport].FilterOn = True
Else
Reports![WorkorderFilterReport].FilterOn = False
End If

End Sub





--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In the Microsoft example, How to Filter a Report from a Pop-Up Form, I'm
trying to filter different data types. The example works great for text
values, but when I try to use it on numerical values or date values I get
the
following error message:

Data type mismatch in criteria expression


Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![WorkorderFilterReport].Filter = strSQL
Reports![WorkorderFilterReport].FilterOn = True
Else
Reports![WorkorderFilterReport].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub

Can this be altered to accept any data type? Or, is the problem with the
configuration of each combo-box?

Thx.