Concatenate criteria strings that refer to multiple fields

R

rupertsland

I am building a search/filter form to make it easier for users to filter data
from an underlying table. The form has a subform, 4 textbox controls (project
name, filing number, report series, and publication year) into which users
can enter criteria, and a 'Search' command button. I figured out how to use
the BuildCriteria method to filter a single field. However, figuring out the
VB code needed to concatenate all 4 criteria strings has been a major
stumbling block.

Microsoft Access 2003's online VisualBasic offers this suggestion:

"...if you wish to construct a criteria string that refers to multiple
fields, you must create the strings and concatenate them yourself. For
example, if you wish to construct criteria for a filter to show records for
orders placed after 1-1-95 and for which freight is less than $50, you would
need to use the BuildCriteria method twice and concatenate the resulting
strings."

How should I properly concatenate the resulting strings?

Also, if a user leaves one or more textbox controls empty, can this create
any problems when you concatenate the strings and apply the filter? If so,
what should I do to ensure the filter takes this into account?

Here's the VB code I wrote, which uses the BuildCriteria method:


strFilterProj = BuildCriteria("PROJ_NAME", dbText, strInputProj)
strFilterFile = BuildCriteria("RPT_FILENUM", dbDouble, strInputFile)
strFilterSeries = BuildCriteria("RPT_SERIES", dbText, strInputSeries)
strFilterPubYear = BuildCriteria("RPT_YEAR", dbDouble, strInputPubYear)

' Concatenate criteria strings
strFilter = (here is where the criteria strings would get concatenated)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True
 
M

Marshall Barton

rupertsland said:
I am building a search/filter form to make it easier for users to filter data
from an underlying table. The form has a subform, 4 textbox controls (project
name, filing number, report series, and publication year) into which users
can enter criteria, and a 'Search' command button. I figured out how to use
the BuildCriteria method to filter a single field. However, figuring out the
VB code needed to concatenate all 4 criteria strings has been a major
stumbling block.

Microsoft Access 2003's online VisualBasic offers this suggestion:

"...if you wish to construct a criteria string that refers to multiple
fields, you must create the strings and concatenate them yourself. For
example, if you wish to construct criteria for a filter to show records for
orders placed after 1-1-95 and for which freight is less than $50, you would
need to use the BuildCriteria method twice and concatenate the resulting
strings."

How should I properly concatenate the resulting strings?

Also, if a user leaves one or more textbox controls empty, can this create
any problems when you concatenate the strings and apply the filter? If so,
what should I do to ensure the filter takes this into account?

Here's the VB code I wrote, which uses the BuildCriteria method:


strFilterProj = BuildCriteria("PROJ_NAME", dbText, strInputProj)
strFilterFile = BuildCriteria("RPT_FILENUM", dbDouble, strInputFile)
strFilterSeries = BuildCriteria("RPT_SERIES", dbText, strInputSeries)
strFilterPubYear = BuildCriteria("RPT_YEAR", dbDouble, strInputPubYear)

' Concatenate criteria strings
strFilter = (here is where the criteria strings would get concatenated)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True
Click to expand...


Actually, it's easier to concatenate the individual filters
as you build the criteria:

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputProj)
End If
If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputFile)
End If
If Not IsNull(txInputSeries) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputSeries)
End If
If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputPubYear)
End If
frm.Filter = Mid(strFilter, 6)
 
R

rupertsland

Hi Marsh.

Thank you for your help. I modified the code, but I still can not get the
filter to work. Here's the full Sub procedure I am using:


Private Sub ApplyFilter_Click()

' Declarations:
Dim frm As Form ' Store form as an object
Dim txtInputProj As String ' Project
Dim txtInputFile As String ' Report Filing number
Dim txtInputSeries As String ' Report series
Dim txtInputPubYear As String ' Report publication year
Dim strFilter As String ' Stored criteria string


' Store criteria entered by user
txtInputProj = [FilterInputRpts_Proj] ' project name
txtInputFile = [FilterInputRpts_File] ' report filing
number
txtInputSeries = [FilterInputRpts_Series] ' report series number
txtInputPubYear = [FilterInputRpts_PubYear] ' publication year


' Associate variable with report list subform object
Set frm = Forms![frm_Switchboard_Admin]![ReportListSubform].Form


' Build criteria string

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND " & BuildCriteria("PROJ_NAME", dbText,
txtInputProj)
End If

If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_FILENUM",
dbDouble, txtInputFile)
End If

If Not IsNull(txtInputSeries) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_SERIES",
dbText, txtInputSeries)
End If

If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_YEAR",
dbDouble, txtInputPubYear)
End If


' Set Filter property to apply filter
frm.Filter = strFilter


' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True

End Sub


Sincerely,
rupertsland

----------------------------------------------------
 
M

Marshall Barton

rupertsland said:
Thank you for your help. I modified the code, but I still can not get the
filter to work. Here's the full Sub procedure I am using:


Private Sub ApplyFilter_Click()

' Declarations:
Dim frm As Form ' Store form as an object
Dim txtInputProj As String ' Project
Dim txtInputFile As String ' Report Filing number
Dim txtInputSeries As String ' Report series
Dim txtInputPubYear As String ' Report publication year
Dim strFilter As String ' Stored criteria string

' Store criteria entered by user
txtInputProj = [FilterInputRpts_Proj] ' project name
txtInputFile = [FilterInputRpts_File] ' report filing
number
txtInputSeries = [FilterInputRpts_Series] ' report series number
txtInputPubYear = [FilterInputRpts_PubYear] ' publication year

' Associate variable with report list subform object
Set frm = Forms![frm_Switchboard_Admin]![ReportListSubform].Form

' Build criteria string

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND " & BuildCriteria("PROJ_NAME", dbText,
txtInputProj)
End If

If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_FILENUM",
dbDouble, txtInputFile)
End If

If Not IsNull(txtInputSeries) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_SERIES",
dbText, txtInputSeries)
End If

If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_YEAR",
dbDouble, txtInputPubYear)
End If

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True

End Sub
Click to expand...


You made a few too many changes.

First, I forgot to explain that my use of the "txt" prefix
was intended to imply the use of the form's text box
directly, not the name of a string variable. Since you
declared the variables as String type, your code will fail
when you try to assign a Null value from an empty text box.
Personally, I see no reason to copy values from one place to
another like that, but if you prefer to do it, make sure you
match up the data types properly. Note that controls are
the Variant datatype to allow them to contain a Null. This
means that you would need to use:
Dim varInputProj As Variant
for the four variables that are copies of the text box
values. Also note that I use the prefix "var" to indicate
the Variant type.

The other thing you left out is the use of the Mid function
to remove the extra " AND " at the beginning the concatened
result of strFilter:
frm.Filter = Mid(strFilter, 6)
 
R

Rob Oldfield

You've missed a bit of Marshall's code. The line

frm.Filter = Mid(strFilter, 6)

chops the first "AND" from the filter string.

You may want to just add a little bit to that to check for where no criteria
are set as well...

if strfilter<>"" then
strfilter=strfilter,6)
endif


rupertsland said:
Hi Marsh.

Thank you for your help. I modified the code, but I still can not get the
filter to work. Here's the full Sub procedure I am using:


Private Sub ApplyFilter_Click()

' Declarations:
Dim frm As Form ' Store form as an object
Dim txtInputProj As String ' Project
Dim txtInputFile As String ' Report Filing number
Dim txtInputSeries As String ' Report series
Dim txtInputPubYear As String ' Report publication year
Dim strFilter As String ' Stored criteria string


' Store criteria entered by user
txtInputProj = [FilterInputRpts_Proj] ' project name
txtInputFile = [FilterInputRpts_File] ' report filing
number
txtInputSeries = [FilterInputRpts_Series] ' report series number
txtInputPubYear = [FilterInputRpts_PubYear] ' publication year


' Associate variable with report list subform object
Set frm = Forms![frm_Switchboard_Admin]![ReportListSubform].Form


' Build criteria string

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND " & BuildCriteria("PROJ_NAME", dbText,
txtInputProj)
End If

If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_FILENUM",
dbDouble, txtInputFile)
End If

If Not IsNull(txtInputSeries) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_SERIES",
dbText, txtInputSeries)
End If

If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_YEAR",
dbDouble, txtInputPubYear)
End If


' Set Filter property to apply filter
frm.Filter = strFilter


' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True

End Sub


Sincerely,
rupertsland

----------------------------------------------------

Marshall Barton said:
Actually, it's easier to concatenate the individual filters
as you build the criteria:

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputProj)
End If
If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputFile)
End If
If Not IsNull(txInputSeries) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputSeries)
End If
If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputPubYear)
End If
frm.Filter = Mid(strFilter, 6)
Click to expand...
Click to expand...
 
M

Marshall Barton

You make a good point there Rob.

While there is no point ot trimming the And from a ZLS or
Null, it would be better to turn off the filter:

If IsNull(varFilter) Then
frm.FilterOn = False
Else
frm.Filter = Mid(strFilter, 6)
frm.FilterOn = True
End If
--
Marsh
MVP [MS Access]


Rob said:
You've missed a bit of Marshall's code. The line

frm.Filter = Mid(strFilter, 6)

chops the first "AND" from the filter string.

You may want to just add a little bit to that to check for where no criteria
are set as well...

if strfilter<>"" then
strfilter=strfilter,6)
endif


Thank you for your help. I modified the code, but I still can not get the
filter to work. Here's the full Sub procedure I am using:

Private Sub ApplyFilter_Click()

' Declarations:
Dim frm As Form ' Store form as an object
Dim txtInputProj As String ' Project
Dim txtInputFile As String ' Report Filing number
Dim txtInputSeries As String ' Report series
Dim txtInputPubYear As String ' Report publication year
Dim strFilter As String ' Stored criteria string


' Store criteria entered by user
txtInputProj = [FilterInputRpts_Proj] ' project name
txtInputFile = [FilterInputRpts_File] ' report filing
number
txtInputSeries = [FilterInputRpts_Series] ' report series number
txtInputPubYear = [FilterInputRpts_PubYear] ' publication year


' Associate variable with report list subform object
Set frm = Forms![frm_Switchboard_Admin]![ReportListSubform].Form


' Build criteria string

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND " & BuildCriteria("PROJ_NAME", dbText,
txtInputProj)
End If

If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_FILENUM",
dbDouble, txtInputFile)
End If

If Not IsNull(txtInputSeries) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_SERIES",
dbText, txtInputSeries)
End If

If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND " & BuildCriteria("RPT_YEAR",
dbDouble, txtInputPubYear)
End If


' Set Filter property to apply filter
frm.Filter = strFilter


' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True

End Sub


Sincerely,
rupertsland

----------------------------------------------------

Marshall Barton said:
rupertsland wrote:

I am building a search/filter form to make it easier for users to filter data
from an underlying table. The form has a subform, 4 textbox controls (project
name, filing number, report series, and publication year) into which users
can enter criteria, and a 'Search' command button. I figured out how to use
the BuildCriteria method to filter a single field. However, figuring out the
VB code needed to concatenate all 4 criteria strings has been a major
stumbling block.

Microsoft Access 2003's online VisualBasic offers this suggestion:

"...if you wish to construct a criteria string that refers to multiple
fields, you must create the strings and concatenate them yourself. For
example, if you wish to construct criteria for a filter to show records for
orders placed after 1-1-95 and for which freight is less than $50, you would
need to use the BuildCriteria method twice and concatenate the resulting
strings."

How should I properly concatenate the resulting strings?

Also, if a user leaves one or more textbox controls empty, can this create
any problems when you concatenate the strings and apply the filter? If so,
what should I do to ensure the filter takes this into account?

Here's the VB code I wrote, which uses the BuildCriteria method:


strFilterProj = BuildCriteria("PROJ_NAME", dbText, strInputProj)
strFilterFile = BuildCriteria("RPT_FILENUM", dbDouble, strInputFile)
strFilterSeries = BuildCriteria("RPT_SERIES", dbText, strInputSeries)
strFilterPubYear = BuildCriteria("RPT_YEAR", dbDouble, strInputPubYear)

' Concatenate criteria strings
strFilter = (here is where the criteria strings would get concatenated)

' Set Filter property to apply filter
frm.Filter = strFilter

' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True


Actually, it's easier to concatenate the individual filters
as you build the criteria:

If Not IsNull(txtInputProj) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputProj)
End If
If Not IsNull(txtInputFile) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputFile)
End If
If Not IsNull(txInputSeries) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputSeries)
End If
If Not IsNull(txtInputPubYear) Then
strFilter = strFilter & " AND (" _
& BuildCriteria("PROJ_NAME", dbText, txtInputPubYear)
End If
frm.Filter = Mid(strFilter, 6)
Click to expand...
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

Using a combo box to display info in a subform 5
MS Download Create QBF-how to use listbox 10

Top