Re: Pulling queriy criteria from a text box

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"MGFoster" <me@xxxxxxxxxxx> wrote in message
news:leiVf.8141$Bj7.4317@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ant wrote:
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-----


.

Relevant Pages

  • Re: exclude statement for query criteria
    ... A different approach would be to add an additional field to your query using ... Instr() function to test whether your string occurs in the data. ... > criteria to: ...
    (microsoft.public.access.gettingstarted)
  • Re: Pulling queriy criteria from a text box
    ... I want to use this value as criteria in a query. ... Here's the usual trick using InStr() in the criteria: ... Change "ColumnName" to the name of the criteria column. ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)