Query Criteria using Text Box Control

[Rebecca_SUNY]

Access 2003

I have a form that has a text box control. I would like to use the result
of this control as criteria in a simple select query. I have the text box
working correctly and if I copy and paste the result into the query criteria,
it works fine but when I reference the text box control as the criteria it
doesn't work.

text box result = "In ("A","B","C")"

text box control = Forms!frm_Form1!Textbox1

I have also tried this with OR instead of IN but it still doesn't work.

 

[Clifford Bass]

Hi Rebecca,

I have puzzled over that in the past. Access's query engine does not
seem to be designed to deal with the In/Not In operator when using
parameters. Parameters are designed to provide a single value and what you
are passing to the query is a single string, not a series of strings. So if
you just put the "[Forms]![frm_Form1]![Textbox1]" in the criteria line you
get:

select * from tblYourTable
where Some_Field = [Forms]![frm_Form1]![Textbox1];

which becomes:

select * from tblYourTable
where Some_Field = "In (""A"",""B"",""C"")";

So this compares the value of Some_Field to the actual string "In
(""A"",""B"",""C"")". And if you try using the "In () in the criteria line
instead of the text box:

select * from tblYourTable
where Some_Field In ([Forms]![frm_Form1]![Textbox1]);

this becomes:

select * from tblYourTable
where Some_Field In ("""A"",""B"",""C""");

So this checks to see if the value of Some_Field is in one item,
specifically """A"",""B"",""C""".

Now, you could probably use a whole bunch of fields, where the user
would enter a single item in each text box:

select * from tblYourTable
where Some_Field In ([Forms]![frm_Form1]![Textbox1],
[Forms]![frm_Form1]![Textbox2], [Forms]![frm_Form1]![Textbox3]);

Or, if you want just to allow the user to type in a list of possible
items (leaving off the "In" and the "(" and ")" --i.e.: A,B,C--you could use
the InStr() function:

select * from tblYourTable
where InStr([Forms]![frm_Form1]![Textbox1], Some_Field) > 0;

Which will work as long as Some_Field cannot contain a comma. You
might have to tweak things to deal with that type of situation.

But, what if you want to allow the user to specify the operator and the
criteria? Examples:
1) = "A"
2) In ("A","B","C")
3) Like "*ABC*"
4) Not In ("D","E")

Here is where you can make use of the Eval() function (example of a
text field):

select * from tblYourTable
where Eval("""" & Replace(Some_Field,"""","""""") & """ " &
[Forms]![frm_Form1]![Textbox1]) = True;

Hope this helps,

Clifford Bass

 

[Rebecca_SUNY]

No, this didn't help. The text box value is a result of VBA code from a list
box and changes each time. Sometimes it will be In ("A","B","C"), sometimes
In ("X","Y","Z"), sometimes In ("A","Z","T"), etc....sometimes it can even be
Not In ("A","X") depending on if another form control is Yes/No.

Would I be better of with OR? I prefer In, because it creates a smaller
string...but I can be flexible....

Hi Rebecca,

I have puzzled over that in the past. Access's query engine does not
seem to be designed to deal with the In/Not In operator when using
parameters. Parameters are designed to provide a single value and what you
are passing to the query is a single string, not a series of strings. So if
you just put the "[Forms]![frm_Form1]![Textbox1]" in the criteria line you
get:

select * from tblYourTable
where Some_Field = [Forms]![frm_Form1]![Textbox1];

which becomes:

select * from tblYourTable
where Some_Field = "In (""A"",""B"",""C"")";

So this compares the value of Some_Field to the actual string "In
(""A"",""B"",""C"")". And if you try using the "In () in the criteria line
instead of the text box:

select * from tblYourTable
where Some_Field In ([Forms]![frm_Form1]![Textbox1]);

this becomes:

select * from tblYourTable
where Some_Field In ("""A"",""B"",""C""");

So this checks to see if the value of Some_Field is in one item,
specifically """A"",""B"",""C""".

Now, you could probably use a whole bunch of fields, where the user
would enter a single item in each text box:

select * from tblYourTable
where Some_Field In ([Forms]![frm_Form1]![Textbox1],
[Forms]![frm_Form1]![Textbox2], [Forms]![frm_Form1]![Textbox3]);

Or, if you want just to allow the user to type in a list of possible
items (leaving off the "In" and the "(" and ")" --i.e.: A,B,C--you could use
the InStr() function:

select * from tblYourTable
where InStr([Forms]![frm_Form1]![Textbox1], Some_Field) > 0;

Which will work as long as Some_Field cannot contain a comma. You
might have to tweak things to deal with that type of situation.

But, what if you want to allow the user to specify the operator and the
criteria? Examples:
1) = "A"
2) In ("A","B","C")
3) Like "*ABC*"
4) Not In ("D","E")

Here is where you can make use of the Eval() function (example of a
text field):

select * from tblYourTable
where Eval("""" & Replace(Some_Field,"""","""""") & """ " &
[Forms]![frm_Form1]![Textbox1]) = True;

Hope this helps,

Clifford Bass

Access 2003

I have a form that has a text box control. I would like to use the result
of this control as criteria in a simple select query. I have the text box
working correctly and if I copy and paste the result into the query criteria,
it works fine but when I reference the text box control as the criteria it
doesn't work.

text box result = "In ("A","B","C")"

text box control = Forms!frm_Form1!Textbox1

I have also tried this with OR instead of IN but it still doesn't work.

 

[Clifford Bass]

Hi Rebecca,

What I suggested with the Eval() function is indeed flexible and should
work for what you are asking. Usage of the Or will not make a difference.
In fact it would actually complicate matters. Again, if you follow it
through using an example of the text box containing:

"A" Or "B" Or "C"

select *
from tblYourTable
where Some_Field = [Forms]![frm_Form1]![Textbox1];

becomes:

select *
from tblYourTable
where Some_Field = ""A" Or "B" Or "C"";

So this compares the contents of Some_Field to the actual string ""A"
Or "B" Or "C"". Incidentally, even using the Eval() function will fail with
this because it would try to evaluate something like:

"A" = "A" Or "B" Or "C"

which is an invalid construct. To use the Or, you would need to end up with:

"A" = "A" Or "A" = "B" Or "A" = "C"

Much more trouble than it is worth.

The source of the text box value does not matter--whether entered by a
human (or monkey ) or set by code. How about posting your query's SQL so
I can look at it to see if it needs adjusting?

Clifford Bass

 

[Rebecca_SUNY]

This monkey -) is still very confused and will need to sit down with the
EVAL and REPLACE functions a little more but the code you provided worked
PERFECTLY...and that is all that really matters, eh? I tested it for the In
and Not In and both worked.

Thanks.

Hi Rebecca,

What I suggested with the Eval() function is indeed flexible and should
work for what you are asking. Usage of the Or will not make a difference.
In fact it would actually complicate matters. Again, if you follow it
through using an example of the text box containing:

"A" Or "B" Or "C"

select *
from tblYourTable
where Some_Field = [Forms]![frm_Form1]![Textbox1];

becomes:

select *
from tblYourTable
where Some_Field = ""A" Or "B" Or "C"";

So this compares the contents of Some_Field to the actual string ""A"
Or "B" Or "C"". Incidentally, even using the Eval() function will fail with
this because it would try to evaluate something like:

"A" = "A" Or "B" Or "C"

which is an invalid construct. To use the Or, you would need to end up with:

"A" = "A" Or "A" = "B" Or "A" = "C"

Much more trouble than it is worth.

The source of the text box value does not matter--whether entered by a
human (or monkey ) or set by code. How about posting your query's SQL so
I can look at it to see if it needs adjusting?

Clifford Bass

No, this didn't help. The text box value is a result of VBA code from a list
box and changes each time. Sometimes it will be In ("A","B","C"), sometimes
In ("X","Y","Z"), sometimes In ("A","Z","T"), etc....sometimes it can even be
Not In ("A","X") depending on if another form control is Yes/No.

Would I be better of with OR? I prefer In, because it creates a smaller
string...but I can be flexible....

 

[Clifford Bass]

Hi Rebecca,

Yep, getting it to work correctly/perfectly is all the really matters.
Glad to hear that you got it to work. Here is something you might try that
will help in understanding what is happening. Create a new column in your
query that displays the text string that gets passed to the Eval() function.

EvalString: """" & Replace(Some_Field,"""","""""") & """ " &
[Forms]![frm_Form1]![Textbox1]

Then run the query to see what is being provided to the Eval()
function. The Replace() function is necessary in case there should ever be a
quote symbol (") in the column being searched, even if you never expect it to
do so. Someone may include it somewhere along the line, even if
unintentionally.

Clifford Bass