problem with IN() syntax

[Guest]

I'm trying to use the IN operator and I having trouble.

I am getting a list of numeric values from a form and using it in a query as
follows:

In ([Forms]![menu_reports]![txtTradeCodes])

If the text box on my form has only 1 value in it it works fine. If it has
more than one value like 1,2,3 my query returns nothing.

If I type In (1,2,3) directly into my query I get back about 100 records.

I've tried about a dozen combinations of double quotes and single quotes
around the digits and around the whole thing and placed the entire IN clause
in the text box but nothing works.

Any help greatly appreciated.

 

[kingston via AccessMonster.com]

I'm not sure whether you mean that the control has the value "1, 2, 3" or
that the control is in a continuous form with different values for each
record. If you mean the former then it's not going to work because the value
of the control is interpreted as text rather than a set of numbers. If you
mean the latter, you're also SOL because Forms!Forms!TextControl only returns
the value in the control on the current form.

If you mean that the control has multiple values within one record, you'll
need to parse the control (e.g. look for values between commas). You can
accomplish what you want in a limited way by using different field criteria:
Val([txtTradeCodes])
Val(Mid([txtTradeCodes],Instr([txtTradeCodes],",")+1))
Val(Mid([txtTradeCodes],Instr(Instr([txtTradeCodes],",")+1,[txtTradeCodes],",
")))
etc.
Trap nulls with IIF conditions and this will work just fine.

If you mean that you want to match values in a number of displayed records,
use the form's .RecordSource property to return the underlying data record
source. Then use the record source as the IN() criteria.

I'm trying to use the IN operator and I having trouble.

I am getting a list of numeric values from a form and using it in a query as
follows:

In ([Forms]![menu_reports]![txtTradeCodes])

If the text box on my form has only 1 value in it it works fine. If it has
more than one value like 1,2,3 my query returns nothing.

If I type In (1,2,3) directly into my query I get back about 100 records.

I've tried about a dozen combinations of double quotes and single quotes
around the digits and around the whole thing and placed the entire IN clause
in the text box but nothing works.

Any help greatly appreciated.

 

[David F Cox]

In ("1,2,3") is not the same as IN(1,2,3) or IN ("1","2","3")
You have work to do.

 

[Guest]

Please do not post stupid / useless responses. It gives the impression that
lots of answers have been submitted to a problem and can disuade helpful
people from looking into a post.

In ("1,2,3") is not the same as IN(1,2,3) or IN ("1","2","3")
You have work to do.

I'm trying to use the IN operator and I having trouble.

I am getting a list of numeric values from a form and using it in a query
as
follows:

In ([Forms]![menu_reports]![txtTradeCodes])

If the text box on my form has only 1 value in it it works fine. If it
has
more than one value like 1,2,3 my query returns nothing.

If I type In (1,2,3) directly into my query I get back about 100 records.

I've tried about a dozen combinations of double quotes and single quotes
around the digits and around the whole thing and placed the entire IN
clause
in the text box but nothing works.

Any help greatly appreciated.

 

[David F Cox]

I believe that if I can get a question askers to think I am doing them a
better service than just presenting them with an answer. My hope was that
either the questioner would realise his error, or ask what I meant if they
were not able to. It is a different style of help. Some times your way works
better, some times mine does. Hopefully this questioner got the benefit of
both approaches. I do not believe that we should be doing peoples work for
them, but helping them to do it for themselves. I will often refer users to
google newsgroups search where they obviously do not know that that resource
exists, or are simply not bothered to use it instead of giving the answer.

Please do not post stupid / useless responses. It gives the impression
that
lots of answers have been submitted to a problem and can disuade helpful
people from looking into a post.

In ("1,2,3") is not the same as IN(1,2,3) or IN ("1","2","3")
You have work to do.

I'm trying to use the IN operator and I having trouble.

I am getting a list of numeric values from a form and using it in a
query
as
follows:

In ([Forms]![menu_reports]![txtTradeCodes])

If the text box on my form has only 1 value in it it works fine. If it
has
more than one value like 1,2,3 my query returns nothing.

If I type In (1,2,3) directly into my query I get back about 100
records.

I've tried about a dozen combinations of double quotes and single
quotes
around the digits and around the whole thing and placed the entire IN
clause
in the text box but nothing works.

Any help greatly appreciated.

 

[Guest]

Thank you. Your response sent me in the right direction.

I used the following and it works like a charm:

In
(Val([Forms]![menu_reports]![txtTradeCodes]),Val(Mid([Forms]![menu_reports]![txtTradeCodes],3)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],5)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],7)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],9)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],11)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],13)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],15)),Val(Mid([Forms]![menu_reports]![txtTradeCodes],17)))

I'm not sure whether you mean that the control has the value "1, 2, 3" or
that the control is in a continuous form with different values for each
record. If you mean the former then it's not going to work because the value
of the control is interpreted as text rather than a set of numbers. If you
mean the latter, you're also SOL because Forms!Forms!TextControl only returns
the value in the control on the current form.

If you mean that the control has multiple values within one record, you'll
need to parse the control (e.g. look for values between commas). You can
accomplish what you want in a limited way by using different field criteria:
Val([txtTradeCodes])
Val(Mid([txtTradeCodes],Instr([txtTradeCodes],",")+1))
Val(Mid([txtTradeCodes],Instr(Instr([txtTradeCodes],",")+1,[txtTradeCodes],",
")))
etc.
Trap nulls with IIF conditions and this will work just fine.

If you mean that you want to match values in a number of displayed records,
use the form's .RecordSource property to return the underlying data record
source. Then use the record source as the IN() criteria.

I'm trying to use the IN operator and I having trouble.

I am getting a list of numeric values from a form and using it in a query as
follows:

In ([Forms]![menu_reports]![txtTradeCodes])

If the text box on my form has only 1 value in it it works fine. If it has
more than one value like 1,2,3 my query returns nothing.

If I type In (1,2,3) directly into my query I get back about 100 records.

I've tried about a dozen combinations of double quotes and single quotes
around the digits and around the whole thing and placed the entire IN clause
in the text box but nothing works.

Any help greatly appreciated.

 

[David F Cox]

and if I had got a response that showed some endeavour I would have
suggested as a test: something like:

instr( ","&[mypicks]&"," , ","&[month1]&"," )>0

which I was about to add to my post when my wife came home.

Please do not post stupid / useless responses. It gives the impression
that
lots of answers have been submitted to a problem and can disuade helpful
people from looking into a post.

In ("1,2,3") is not the same as IN(1,2,3) or IN ("1","2","3")
You have work to do.

I'm trying to use the IN operator and I having trouble.

I am getting a list of numeric values from a form and using it in a
query
as
follows:

In ([Forms]![menu_reports]![txtTradeCodes])

If the text box on my form has only 1 value in it it works fine. If it
has
more than one value like 1,2,3 my query returns nothing.

If I type In (1,2,3) directly into my query I get back about 100
records.

I've tried about a dozen combinations of double quotes and single
quotes
around the digits and around the whole thing and placed the entire IN
clause
in the text box but nothing works.

Any help greatly appreciated.