PopUp form to filter report

L

Liz

I have a popup form that filters fields in for a report,
the user chooses the appropriate info from a combo box and
clicks a Filter button to perform the actual filter. This
works perfectly for any fields in the underlying table
that are text, but I get a data type mismatch error for
fields that are Yes/No or numeric.
The code attached to the filter button is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Set_Filter_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![zrptIssue Log].Filter = strSQL
Reports![zrptIssue Log].FilterOn = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is appreciated!
Thanks in advance.
Liz
 
J

John Nurick

Hi Liz,

You're wrapping both text and numeric values in " "; the latter must be
bare, as in
Filter1 = "ABCD" AND Filter2 = 1234

You'll need to monitor the data types and build up the SQL WHERE
condition accordingly.


#On Fri, 28 May 2004 08:51:39 -0700, "Liz"
I have a popup form that filters fields in for a report,
the user chooses the appropriate info from a combo box and
clicks a Filter button to perform the actual filter. This
works perfectly for any fields in the underlying table
that are text, but I get a data type mismatch error for
fields that are Yes/No or numeric.
The code attached to the filter button is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Set_Filter_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![zrptIssue Log].Filter = strSQL
Reports![zrptIssue Log].FilterOn = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is appreciated!
Thanks in advance.
Liz
Click to expand...
 
L

Liz

Hi John,
Thanks for the response.
I understand that I am currently wrapping everything into
text....I'm not very good at VB, so I'm not certain how I
go about changing from 1 filter to the next. I will be
using Text, dates and numbers.
Any further help you can throw my way?
Thanks again!
Liz
-----Original Message-----
Hi Liz,

You're wrapping both text and numeric values in " "; the latter must be
bare, as in
Filter1 = "ABCD" AND Filter2 = 1234

You'll need to monitor the data types and build up the SQL WHERE
condition accordingly.


#On Fri, 28 May 2004 08:51:39 -0700, "Liz"
I have a popup form that filters fields in for a report,
the user chooses the appropriate info from a combo box and
clicks a Filter button to perform the actual filter. This
works perfectly for any fields in the underlying table
that are text, but I get a data type mismatch error for
fields that are Yes/No or numeric.
The code attached to the filter button is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Set_Filter_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![zrptIssue Log].Filter = strSQL
Reports![zrptIssue Log].FilterOn = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is appreciated!
Thanks in advance.
Liz
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
J

John Nurick

Hi Liz,

Assuming the form is bound to the table in question, you can get the
data type of any field and add the correct delimiters by using something
like this (untested air code) in place of the present strSQL = strSQL &
.... statement


Dim lngFType As Long
Dim strFName As String
Dim strValue As String
...

'get the name of the field(I think this does the same as your
'present code
strFName = "[" Me.Controls("Filter" & intCounter).Tag & "]"
'get its data type from the form's RecordsetClone
'or in Access 2002 and later, use its Recordset property
lngFType = Me.RecordsetClone.Fields(strFName).Type

Select Case lngFType
Case dbText, dbMemo '10
strValue = Chr(34) & _
Me.Controls(strFName).Value & Chr(34)
Case dbDate '8
strValue = "#" _
& Format(Me.Controls(strFName).Value, "yyyy/mm/dd") _
& "#"
Case Else 'i.e. all numeric fields inc booleans
strValue = Me.Controls(strFName).Value
End Select
strSQL = strSQL & strFName & " = " strValue & " AND "



Hi John,
Thanks for the response.
I understand that I am currently wrapping everything into
text....I'm not very good at VB, so I'm not certain how I
go about changing from 1 filter to the next. I will be
using Text, dates and numbers.
Any further help you can throw my way?
Thanks again!
Liz
-----Original Message-----
Hi Liz,

You're wrapping both text and numeric values in " "; the latter must be
bare, as in
Filter1 = "ABCD" AND Filter2 = 1234

You'll need to monitor the data types and build up the SQL WHERE
condition accordingly.


#On Fri, 28 May 2004 08:51:39 -0700, "Liz"
I have a popup form that filters fields in for a report,
the user chooses the appropriate info from a combo box and
clicks a Filter button to perform the actual filter. This
works perfectly for any fields in the underlying table
that are text, but I get a data type mismatch error for
fields that are Yes/No or numeric.
The code attached to the filter button is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Set_Filter_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![zrptIssue Log].Filter = strSQL
Reports![zrptIssue Log].FilterOn = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is appreciated!
Thanks in advance.
Liz
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
L

Liz

Hi John,
I will give that a try.
The form actually isn't bound to the table....I have
unbound combo boxes where the user can choose the item
from a list from the underlying table, then click a
command button that sets the Filter.
Thanks again, have a great day.
Liz
-----Original Message-----
Hi Liz,

Assuming the form is bound to the table in question, you can get the
data type of any field and add the correct delimiters by using something
like this (untested air code) in place of the present strSQL = strSQL &
.... statement


Dim lngFType As Long
Dim strFName As String
Dim strValue As String
...

'get the name of the field(I think this does the same as your
'present code
strFName = "[" Me.Controls("Filter" & intCounter).Tag & "]"
'get its data type from the form's RecordsetClone
'or in Access 2002 and later, use its Recordset property
lngFType = Me.RecordsetClone.Fields(strFName).Type

Select Case lngFType
Case dbText, dbMemo '10
strValue = Chr(34) & _
Me.Controls(strFName).Value & Chr(34)
Case dbDate '8
strValue = "#" _
& Format(Me.Controls
Click to expand...
(strFName).Value, "yyyy/mm/dd") _
& "#"
Case Else 'i.e. all numeric fields inc booleans
strValue = Me.Controls(strFName).Value
End Select
strSQL = strSQL & strFName & " = " strValue & " AND "



Hi John,
Thanks for the response.
I understand that I am currently wrapping everything into
text....I'm not very good at VB, so I'm not certain how I
go about changing from 1 filter to the next. I will be
using Text, dates and numbers.
Any further help you can throw my way?
Thanks again!
Liz
-----Original Message-----
Hi Liz,

You're wrapping both text and numeric values in " ";
Click to expand...
the
latter must be
bare, as in
Filter1 = "ABCD" AND Filter2 = 1234

You'll need to monitor the data types and build up the SQL WHERE
condition accordingly.


#On Fri, 28 May 2004 08:51:39 -0700, "Liz"

I have a popup form that filters fields in for a report,
the user chooses the appropriate info from a combo box and
clicks a Filter button to perform the actual filter. This
works perfectly for any fields in the underlying table
that are text, but I get a data type mismatch error for
fields that are Yes/No or numeric.
The code attached to the filter button is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Set_Filter_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![zrptIssue Log].Filter = strSQL
Reports![zrptIssue Log].FilterOn = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is appreciated!
Thanks in advance.
Liz

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
J

John Nurick

"Underlying table" made me think it was bound. Instead of using
Me.RecordsetClone to get the field data types, use the table's TableDef,
e.g. as you get started do something like this:

Dim dbD as DAO.Database
Dim tdfT as Tabledef

Set dbD = CurrentDB()
Set tdfT = dbD.TableDefs("tblMyTable")

and then in the For...Next loop replace
lngFType = Me.RecordsetClone.Fields(strFName).Type
with
lngFType = tdfT.Fields(strFName).Type

Hi John,
I will give that a try.
The form actually isn't bound to the table....I have
unbound combo boxes where the user can choose the item
from a list from the underlying table, then click a
command button that sets the Filter.
Thanks again, have a great day.
Liz
-----Original Message-----
Hi Liz,

Assuming the form is bound to the table in question, you can get the
data type of any field and add the correct delimiters by using something
like this (untested air code) in place of the present strSQL = strSQL &
.... statement


Dim lngFType As Long
Dim strFName As String
Dim strValue As String
...

'get the name of the field(I think this does the same as your
'present code
strFName = "[" Me.Controls("Filter" & intCounter).Tag & "]"
'get its data type from the form's RecordsetClone
'or in Access 2002 and later, use its Recordset property
lngFType = Me.RecordsetClone.Fields(strFName).Type

Select Case lngFType
Case dbText, dbMemo '10
strValue = Chr(34) & _
Me.Controls(strFName).Value & Chr(34)
Case dbDate '8
strValue = "#" _
& Format(Me.Controls
Click to expand...
(strFName).Value, "yyyy/mm/dd") _
& "#"
Case Else 'i.e. all numeric fields inc booleans
strValue = Me.Controls(strFName).Value
End Select
strSQL = strSQL & strFName & " = " strValue & " AND "



Hi John,
Thanks for the response.
I understand that I am currently wrapping everything into
text....I'm not very good at VB, so I'm not certain how I
go about changing from 1 filter to the next. I will be
using Text, dates and numbers.
Any further help you can throw my way?
Thanks again!
Liz
-----Original Message-----
Hi Liz,

You're wrapping both text and numeric values in " "; the
latter must be
bare, as in
Filter1 = "ABCD" AND Filter2 = 1234

You'll need to monitor the data types and build up the
SQL WHERE
condition accordingly.


#On Fri, 28 May 2004 08:51:39 -0700, "Liz"

I have a popup form that filters fields in for a report,
the user chooses the appropriate info from a combo box
and
clicks a Filter button to perform the actual filter.
This
works perfectly for any fields in the underlying table
that are text, but I get a data type mismatch error for
fields that are Yes/No or numeric.
The code attached to the filter button is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Set_Filter_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![zrptIssue Log].Filter = strSQL
Reports![zrptIssue Log].FilterOn = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is appreciated!
Thanks in advance.
Liz

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 

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

Similar Threads

Filter Report by Form - VBA Code 1
Data type mismatch in criteira expression - HUH? 4
Improper coding for my Form Filter 8
Filter a form using a popup form 0
Report Filter Via a Form? Help? 5
Type Mismatch 1
Form Filter... "Enter Parameter Value" ?? 5
Using a filter to show results on a report 6

Top