RUNNING A QUERY WITH CRITERIA FROM A TEXT BOX ON A FORM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have a form set up which includes various combo boxes and one
text box which a query uses as criteria to run. All of my combo boxes are
working and my text box works provided there is only one criteria listed.
For example, my text box is so a user can enter branch numbers for which they
would like to view data. In the associated query, I have the criteria field
set to Like [forms]![FORMNAME]![FIELDNAME].

The issue I am having is that even if there is more than one branch listed,
like 7575 and 7576, the query will run for the first branch number listed but
will not include data for two. I've tried it several ways by entering "7575,
7576", "7575 or 7576", and "7575 7576". All of these variations return the
same data for the first branch only.

Is there a way to enter several branches seperated by commas so that Access
will pull data for each individual branch without having to run an append
query after each branch is run.

The one thing that confuses me greatly is that I can enter "Like 7575 or
Like 7576" in the criteria field instead of pointing the criteria field to
pull from the form and the query runs perfectly. It just won't pull the data
if I point the criteria field at the form to pull the data a user has entered.
 
Is is my understanding that you cannot use AND OR BETWEEN and other things
like that when you are referencing a control on a form. Maybe you could try
creating several text boxes and in the query add these as the OR if that
makes sense.

In query design grid just add the next reference to the next txt box in the
box below, or in the sql in the where have an OR.
 
Texas_Access_User said:
I currently have a form set up which includes various combo boxes and one
text box which a query uses as criteria to run. All of my combo boxes are
working and my text box works provided there is only one criteria listed.
For example, my text box is so a user can enter branch numbers for which they
would like to view data. In the associated query, I have the criteria field
set to Like [forms]![FORMNAME]![FIELDNAME].

The issue I am having is that even if there is more than one branch listed,
like 7575 and 7576, the query will run for the first branch number listed but
will not include data for two. I've tried it several ways by entering "7575,
7576", "7575 or 7576", and "7575 7576". All of these variations return the
same data for the first branch only.

Is there a way to enter several branches seperated by commas so that Access
will pull data for each individual branch without having to run an append
query after each branch is run.

The one thing that confuses me greatly is that I can enter "Like 7575 or
Like 7576" in the criteria field instead of pointing the criteria field to
pull from the form and the query runs perfectly. It just won't pull the data
if I point the criteria field at the form to pull the data a user has entered.
Click to expand...


You can only use one wildcard pattern on the right of the
Like operator. The string on the left can contain just
about anything though.

Putting that together with what you want to do, try creating
a calculated field in the query:

Forms![the form]!textbox LIKE "*" & [branch] & "*"

And set its criteria to:
=True
 
The code below is what another person in my organization has written and it
works to seperate out branch numbers provided they are entered with a comma
between them. Though I wish I was better at actually writing code I am
unfortunantly not quite experienced enough to understand exactly what he did
here. I am attempted to find the FormatListString but can find no mention of
it to see what it does. Can anyone explain to me how this could take
branches entered like "1001, 1002, 1003, 1004, 1005" and extract and filter
based upon the four digit numbers entered?


If Not (IsNull(tbxBranchID) Or IsEmpty(tbxBranchID)) Then
If where_clause <> "" Then
where_clause = IIf(Me.grpConj1 = 1, " AND ", "OR ") & where_clause
End If
where_clause = "pbs_inventory.branch_id " &
FormatListString(tbxBranchID, True, Me.Check53) & where_clause
End If
 
Back
Top