Creating a query with Multi select list boxes

[BWD]

Hi Group,

In a bind here; I have a form with 4 multi select list boxes. What I
would like to do is create a query that utilizes the values selected
in the 4 list boxes. I have gotten as far as creating a text box on
my form that records the values selected in the multi list. This text
box value is in the format of "Value1" or "Value2" or "Value3" etc.

However, setting the query parameter to my text box value does not
yield the results I am looking for. Can anyone provide some
guidance??

Thanks much!

 

[Stuart McCall]

Hi Group,

In a bind here; I have a form with 4 multi select list boxes. What I
would like to do is create a query that utilizes the values selected
in the 4 list boxes. I have gotten as far as creating a text box on
my form that records the values selected in the multi list. This text
box value is in the format of "Value1" or "Value2" or "Value3" etc.

However, setting the query parameter to my text box value does not
yield the results I am looking for. Can anyone provide some
guidance??

Thanks much!

First of all, change your code that fills your textboxes so that they
contain comma-separated lists of values, ie:

"Value1","Value2","Value3"

Then construct your query's SQL something like this:

SELECT <fieldlist> FROM <tablename> WHERE <fieldname> IN(" & <textbox1> & ")
AND <fieldname> IN(" & <textbox2> & ")"
etc.

Replace everything in angle brackets with your real names.

 

[BWD]

Thanks for your reponse. I made the changes to the text field value
and then inserted the "IN" statement to the query's SQL. This did not
produce results.

I then tried to manually enter the string values:
"Value1","Value2","Value3" etc. into the paramter field in the QBD
grid. The query did not like this format it would only produce
results when I specifically entered values in the fomat of; "Value1"
OR "Value2" OR "Value3" etc.

Perhaps it is the way I am writing the "IN" part of the SQL
statement?

Again, any help would be appreciated.

 

[BWD]

Thanks for your reponse. I made the changes to the text field value
and then inserted the "IN" statement to the query's SQL. This did not
produce results.

I then tried to manually enter the string values:
"Value1","Value2","Value3" etc. into the paramter field in the QBD
grid. The query did not like this format it would only produce
results when I specifically entered values in the fomat of; "Value1"
OR "Value2" OR "Value3" etc.

Perhaps it is the way I am writing the "IN" part of the SQL
statement?

Again, any help would be appreciated.

ADDITION TO LAST MESSAGE:

Perhaps it will be helpful to add the SQL that I generated for this:

SELECT *
FROM [Table Name]
WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));

This SQL is still not working for me.
Thanks

 

[Guest]

Let me get my head around this. You have four list boxes, each of which
generates a selected value. Are all those values to be applied as criteria
against ONE field in your query, or against DIFFERENT fields? In my
experience the latter seems more likely. Please clarify.

 

[Stuart McCall]

Thanks for your reponse. I made the changes to the text field value
and then inserted the "IN" statement to the query's SQL. This did not
produce results.

I then tried to manually enter the string values:
"Value1","Value2","Value3" etc. into the paramter field in the QBD
grid. The query did not like this format it would only produce
results when I specifically entered values in the fomat of; "Value1"
OR "Value2" OR "Value3" etc.

Perhaps it is the way I am writing the "IN" part of the SQL
statement?

Again, any help would be appreciated.

ADDITION TO LAST MESSAGE:

Perhaps it will be helpful to add the SQL that I generated for this:

SELECT *
FROM [Table Name]
WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));

This SQL is still not working for me.
Thanks

The problem here is the equals sign. Get rid of it. "In", used in this
context, is an operator, as is the equals sign.

SELECT *
FROM [Table Name]
WHERE Table].FieldName In (" & FormName.FieldName & ")";

For clarity, I've removed the parantheses that the query designer adds. It's
ok to remove them because the designer only uses them internally, to make
its parsing more precise.

Hope that cracks it.

 

[BWD]

ADDITION TO LAST MESSAGE:

Perhaps it will be helpful to add the SQL that I generated for this:

SELECT *
FROM [Table Name]
WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));

This SQL is still not working for me.
Thanks

The problem here is the equals sign. Get rid of it. "In", used in this
context, is an operator, as is the equals sign.

SELECT *
FROM [Table Name]
WHERE Table].FieldName In (" & FormName.FieldName & ")";

For clarity, I've removed the parantheses that the query designer adds. It's
ok to remove them because the designer only uses them internally, to make
its parsing more precise.

Hope that cracks it.- Hide quoted text -

- Show quoted text -

Unfortunately this does not seem to get it to work. I can pass a
single value to the query but it will not accept the list!

Anyone?

 

[Stuart McCall]

Thanks for your reponse. I made the changes to the text field value
and then inserted the "IN" statement to the query's SQL. This did not
produce results.

I then tried to manually enter the string values:
"Value1","Value2","Value3" etc. into the paramter field in the QBD
grid. The query did not like this format it would only produce
results when I specifically entered values in the fomat of; "Value1"
OR "Value2" OR "Value3" etc.

Perhaps it is the way I am writing the "IN" part of the SQL
statement?

Again, any help would be appreciated.

ADDITION TO LAST MESSAGE:

Perhaps it will be helpful to add the SQL that I generated for this:

SELECT *
FROM [Table Name]
WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));

This SQL is still not working for me.
Thanks

The problem here is the equals sign. Get rid of it. "In", used in this
context, is an operator, as is the equals sign.

SELECT *
FROM [Table Name]
WHERE Table].FieldName In (" & FormName.FieldName & ")";

For clarity, I've removed the parantheses that the query designer adds.
It's
ok to remove them because the designer only uses them internally, to make
its parsing more precise.

Hope that cracks it.- Hide quoted text -

- Show quoted text -

Unfortunately this does not seem to get it to work. I can pass a
single value to the query but it will not accept the list!

Anyone?

Sorry, for some reason I got it into my head that you were building the SQL
in code (that's why the quotes and ampersands). I think this ought to do it:

SELECT *
FROM [Table Name]
WHERE

.FieldName In (Forms!FormName.FieldName);

 

[BWD]

Thanks for your reponse. I made the changes to the text field value
and then inserted the "IN" statement to the query's SQL. This did not
produce results.
I then tried to manually enter the string values:
"Value1","Value2","Value3" etc. into the paramter field in the QBD
grid. The query did not like this format it would only produce
results when I specifically entered values in the fomat of; "Value1"
OR "Value2" OR "Value3" etc.
Perhaps it is the way I am writing the "IN" part of the SQL
statement?
Again, any help would be appreciated.
ADDITION TO LAST MESSAGE:
Perhaps it will be helpful to add the SQL that I generated for this:
SELECT *
FROM [Table Name]
WHERE (((Table].FieldName)="In (" & FormName.FieldName & ")"));
This SQL is still not working for me.
Thanks
The problem here is the equals sign. Get rid of it. "In", used in this
context, is an operator, as is the equals sign.
SELECT *
FROM [Table Name]
WHERE Table].FieldName In (" & FormName.FieldName & ")";
For clarity, I've removed the parantheses that the query designer adds.
It's
ok to remove them because the designer only uses them internally, to make
its parsing more precise.
Hope that cracks it.- Hide quoted text -
- Show quoted text -

Unfortunately this does not seem to get it to work. I can pass a
single value to the query but it will not accept the list!

Anyone?

Sorry, for some reason I got it into my head that you were building the SQL
in code (that's why the quotes and ampersands). I think this ought to do it:

SELECT *
FROM [Table Name]
WHERE

.FieldName In (Forms!FormName.FieldName);- Hide quoted text -

- Show quoted text -

I thought that would do it as well, but it does not. I also tried
manually placing the values in the field that my query was referring
to;"Value1","Value2","Value3. Even this did not work. The parameter
only accepted 1 value at a time. I could not pass multiple values in
the string.

Very confounding!

 

[Stuart McCall]

Sorry, for some reason I got it into my head that you were building the
SQL
in code (that's why the quotes and ampersands). I think this ought to do
it:

SELECT *
FROM [Table Name]
WHERE

.FieldName In (Forms!FormName.FieldName);- Hide quoted
text -

- Show quoted text -

I thought that would do it as well, but it does not. I also tried
manually placing the values in the field that my query was referring
to;"Value1","Value2","Value3. Even this did not work. The parameter
only accepted 1 value at a time. I could not pass multiple values in
the string.

Very confounding!

I can't be sure but I have a suspicion that you're using the wrong cell in
the query deigner grid. Try pasting this SQL into the designer (switch to
SQL view first) :

SELECT *
FROM [Table Name]
WHERE

.FieldName In ("Value1","Value2","Value3");

Substitute your actual table and field names. Switch back to design view and
note where the In ("Value1","Value2","Value3") is located.

Does that help?

 

[BWD]

<SNIP>

Sorry, for some reason I got it into my head that you were building the
SQL
in code (that's why the quotes and ampersands). I think this ought to do
it:
SELECT *
FROM [Table Name]
WHERE

.FieldName In (Forms!FormName.FieldName);- Hide quoted
text -
- Show quoted text -

I thought that would do it as well, but it does not. I also tried
manually placing the values in the field that my query was referring
to;"Value1","Value2","Value3. Even this did not work. The parameter
only accepted 1 value at a time. I could not pass multiple values in
the string.

Very confounding!

I can't be sure but I have a suspicion that you're using the wrong cell in
the query deigner grid. Try pasting this SQL into the designer (switch to
SQL view first) :

SELECT *
FROM [Table Name]
WHERE

.FieldName In ("Value1","Value2","Value3");

Substitute your actual table and field names. Switch back to design view and
note where the In ("Value1","Value2","Value3") is located.

Does that help?- Hide quoted text -

- Show quoted text -

That works, I get the reponse correctly.

However, I can not get the parameter query to grab that string from
the field being referenced on my form and have the query return the
correct value.

I am going to repost this message and see what others come up with.
Thanks for all the replies, development can be a tedious task and
these boards and the posters on them are great resources!!