Using Text Box to Enter Criteria

[AccessIM]

Hello Everyone-

I have created a form (frmReportCriteriaMenu) to select specific criteria to
be used in a query (qryRouteLoad).

On my form, I have two unbound text boxes to choose the beginning date and
ending date the user wants. In my query, I am using "Between
forms!frmReportCriteriaMenu!BeginningDate and
forms!frmReportCriteriaMenu!EndingDate" as criteria under the Date field.
This works just fine.

The next part of my form has a text box (txtEnterRoutes) that I would like
to use as criteria for the Route field in the same query.

Currently, I am using forms!frmReportCriteriaMenu!txtEnterRoutes as criteria
under the Route field and it works great when I enter one route number.
However, 99% of the time, the user needs to run information for a list of
route numbers. I changed the "Enter Key Behavior" line on the text box to
"New Line in Field" but, when I enter more than one route number in the text
box, the query comes up blank.

Is it possible to enter a list of numbers in the text box and have the query
pull all of these route numbers out of the table? If so, can someone tell me
where I went wrong?

Thank you so much!

 

[Klatuu]

The problem is not in the form or the text box, it is how the query is seeing
what you have entered. You are probably using something like:
WHERE [SomeField] = forms!frmReportCriteriaMenu!txtEnterRoutes

When you put that in using one route, it will be fine. When you put in
additional values, regardless of how you separate them, it will not find a
match. If you want to filter for more than one value it has to be:
WHERE [SomeField] IN (3, 5, 7, 11, 12)

So you have to be able to present it to Jet in that format. In a similar
case, I used a Multi Select List Box that presents all the possible options
to the user and the user selects the desired values. Then I have a function
that evaluates the list box and builds the WHERE part of the SQL string for
you. It is written like it is because in this case, there are 6 list boxes
from which the user may select criteria

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[Dirk Goldgar]

Hello Everyone-

I have created a form (frmReportCriteriaMenu) to select specific criteria
to
be used in a query (qryRouteLoad).

On my form, I have two unbound text boxes to choose the beginning date and
ending date the user wants. In my query, I am using "Between
forms!frmReportCriteriaMenu!BeginningDate and
forms!frmReportCriteriaMenu!EndingDate" as criteria under the Date field.
This works just fine.

The next part of my form has a text box (txtEnterRoutes) that I would like
to use as criteria for the Route field in the same query.

Currently, I am using forms!frmReportCriteriaMenu!txtEnterRoutes as
criteria
under the Route field and it works great when I enter one route number.
However, 99% of the time, the user needs to run information for a list of
route numbers. I changed the "Enter Key Behavior" line on the text box to
"New Line in Field" but, when I enter more than one route number in the
text
box, the query comes up blank.

Is it possible to enter a list of numbers in the text box and have the
query
pull all of these route numbers out of the table? If so, can someone tell
me
where I went wrong?

Thank you so much!

This can be done, but it's a bit tricky. You can build a rather inefficient
criterion that goes something like this:

WHERE "," & Replace(Forms!frmReportCriteriaMenu!txtEnterRoutes, " ", "")
& ","
LIKE "*," & [Route] & ",*"
OR Forms!frmReportCriteriaMenu!txtEnterRoutes Is Null

(I hope I got that right.) That would allow you to enter a list of
comma-separated route numbers in txtEnterRoutes. Note that it assumes that
the [Route] field is numeric, not text, and would require modification to
add quotes if this is a text field.

Be aware that this is an inefficient query method. If possible, you would
do better to rewrite the query or the report's RecordSource on the fly. If
this is for a report, you could use the report's Open event to modify the
report's RecordSource property. For example,

'----- start of example code -----
Private Sub Report_Open(Cancel As Integer)

Dim strCriteria As String

If CurrentProject.AllForms("frmReportCriteriaMenu").IsLoaded Then

With Forms!frmReportCriteriaMenu!txtEnterRoutes
If Not IsNull(.Value) Then
strCriteria = strCriteria & " AND " & _
"([Route] In (" & .Value & "))"
End If
End With

End If

If Len(strCriteria) = 0 Then
Me.RecordSource = "qryRouteLoad"
Else
' Drop leading " AND " from strCriteria and make it a WHERE clause.
Me.RecordSource = _
"SELECT * FROM qryRouteLoad WHERE " & _
Mid$(strCriteria, 6)
End If

End Sub
'----- end of example code -----

 

[AccessIM]

Hi Dirk-

Thank you for the suggestions.

Due to time restraints and my inexperience with writing code, I went with
the "inefficient" solution because I could get it to work without any problem.

I plan on playing around with your second suggestion as well as the
suggestion from Dave Hargis for the multi select list box. I have been
trying to do multi select list boxes for some time now and just can't seem to
get it right but I am determined to figure it out! )

Thank you both for your suggestions!

Hello Everyone-

I have created a form (frmReportCriteriaMenu) to select specific criteria
to
be used in a query (qryRouteLoad).

On my form, I have two unbound text boxes to choose the beginning date and
ending date the user wants. In my query, I am using "Between
forms!frmReportCriteriaMenu!BeginningDate and
forms!frmReportCriteriaMenu!EndingDate" as criteria under the Date field.
This works just fine.

The next part of my form has a text box (txtEnterRoutes) that I would like
to use as criteria for the Route field in the same query.

Currently, I am using forms!frmReportCriteriaMenu!txtEnterRoutes as
criteria
under the Route field and it works great when I enter one route number.
However, 99% of the time, the user needs to run information for a list of
route numbers. I changed the "Enter Key Behavior" line on the text box to
"New Line in Field" but, when I enter more than one route number in the
text
box, the query comes up blank.

Is it possible to enter a list of numbers in the text box and have the
query
pull all of these route numbers out of the table? If so, can someone tell
me
where I went wrong?

Thank you so much!

This can be done, but it's a bit tricky. You can build a rather inefficient
criterion that goes something like this:

WHERE "," & Replace(Forms!frmReportCriteriaMenu!txtEnterRoutes, " ", "")
& ","
LIKE "*," & [Route] & ",*"
OR Forms!frmReportCriteriaMenu!txtEnterRoutes Is Null

(I hope I got that right.) That would allow you to enter a list of
comma-separated route numbers in txtEnterRoutes. Note that it assumes that
the [Route] field is numeric, not text, and would require modification to
add quotes if this is a text field.

Be aware that this is an inefficient query method. If possible, you would
do better to rewrite the query or the report's RecordSource on the fly. If
this is for a report, you could use the report's Open event to modify the
report's RecordSource property. For example,

'----- start of example code -----
Private Sub Report_Open(Cancel As Integer)

Dim strCriteria As String

If CurrentProject.AllForms("frmReportCriteriaMenu").IsLoaded Then

With Forms!frmReportCriteriaMenu!txtEnterRoutes
If Not IsNull(.Value) Then
strCriteria = strCriteria & " AND " & _
"([Route] In (" & .Value & "))"
End If
End With

End If

If Len(strCriteria) = 0 Then
Me.RecordSource = "qryRouteLoad"
Else
' Drop leading " AND " from strCriteria and make it a WHERE clause.
Me.RecordSource = _
"SELECT * FROM qryRouteLoad WHERE " & _
Mid$(strCriteria, 6)
End If

End Sub
'----- end of example code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)