Re: Using Text Box to Enter Criteria
- From: AccessIM <AccessIM@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 13 Jul 2009 10:03:01 -0700
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! :o)
Thank you both for your suggestions!
"Dirk Goldgar" wrote:
"AccessIM" <AccessIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message.
news:BD156703-CA72-4180-A356-F6BCB50FA9C2@xxxxxxxxxxxxxxxx
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)
- References:
- Using Text Box to Enter Criteria
- From: AccessIM
- Re: Using Text Box to Enter Criteria
- From: Dirk Goldgar
- Using Text Box to Enter Criteria
- Prev by Date: Re: ADH paired listbox formatting
- Next by Date: Re: Me.AllowEdits with ComboBox
- Previous by thread: Re: Using Text Box to Enter Criteria
- Next by thread: RE: access table connected to excel form
- Index(es):
Relevant Pages
|
