Pulling queriy criteria from a text box

[Ant]

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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRCWwDYechKqOuFEgEQJrpgCgjQUqF5ULyjtNJQ+gJElGTpzxFQ8An3wV
BH1MmDFOSnfwNyiPhB1S6p72
=qGd/
-----END PGP SIGNATURE-----

 

[Ant]

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
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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRCWwDYechKqOuFEgEQJrpgCgjQUqF5ULyjtNJQ+gJElGTpzxFQ8An3wV
BH1MmDFOSnfwNyiPhB1S6p72
=qGd/
-----END PGP SIGNATURE-----