Pulling queriy criteria from a text box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ant - 25 Mar 2006 14:46 GMT

I have an unbound text box on a form (It will be hidden) a typical value in
the text box could be 1,3,4,5. I want to use this value as criteria in a
query. E.g show records 1,3,4,5

If in my query I manually type IN (1,3,4,5) the query works fine however if
in the criteria line of the query I use IN
([Forms]![frm_Group_PickList]![Result]) In other words use the value in my
text box the query will does not work. I have noticed that it works if only
one number is in the text box.

How can I use the value in my text box as criteria in my query?

Thanks for any help.

Reply to this Message

MGFoster - 25 Mar 2006 22:03 GMT

> I have an unbound text box on a form (It will be hidden) a typical value in
> the text box could be 1,3,4,5. I want to use this value as criteria in a
[quoted text clipped - 5 lines]
> text box the query will does not work. I have noticed that it works if only
> one number is in the text box.

Your parameter is returning the string

"1,3,4,5"

which means the query is searching for the value "1,3,4,5" instead of a
1 or a 3 or a 4 or a 5.

Each number can't be "seen" using the reference to the form's control,
because that is interpreted as one value instead of 4 values.

Here's the usual trick using InStr() in the criteria (SQL view):

SELECT...
FROM...
WHERE InStr("," & Forms!frm_Group_PickList!Result & "," , "," &
ColumnName & ",") > 0

Change "ColumnName" to the name of the criteria column.

What the InStr() function does is determine if the value of the
ColumnName is in the string returned by the reference to the form's
TextBox. If it is InStr() returns a value > 0, which causes the WHERE
clause to evaluate to True, which means that row is selected.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message

Ant - 25 Mar 2006 22:18 GMT

Thanks, I'll have a go, I have been trying to solve this one all day!

>> I have an unbound text box on a form (It will be hidden) a typical value
>> in the text box could be 1,3,4,5. I want to use this value as criteria
[quoted text clipped - 32 lines]
> TextBox. If it is InStr() returns a value > 0, which causes the WHERE
> clause to evaluate to True, which means that row is selected.

Reply to this Message