RE: Using Text Box to Enter Criteria
- From: Klatuu <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 9 Jul 2009 13:40:01 -0700
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
--
Dave Hargis, Microsoft Access MVP
"AccessIM" wrote:
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!
- References:
- Using Text Box to Enter Criteria
- From: AccessIM
- Using Text Box to Enter Criteria
- Prev by Date: Re: Update Multiple Forms
- Next by Date: Re: Using Text Box to Enter Criteria
- Previous by thread: Using Text Box to Enter Criteria
- Next by thread: Re: Using Text Box to Enter Criteria
- Index(es):
Relevant Pages
|
