Query criteria from Multi Select Listbox

[Chuck]

A97, One Table (Table1), Field to be filtered is a three character text file
named Abc.
Form1 has a Listbox named ctla and a Textbox named Texta.
Listbox gets values of Abc from a query of Table1 showing field Abc, group by
and Abc, count.

If I hard wire the criteria for field Abc to AAA, the query runs correctly.
If I hard wire the criteria for field Abc to "AAA", the query does not return
any data. Note the quotes.
If I hard wire the criteria for field Abc to "AAA" Or "BBB", the query runs
correctly. Note the quotes.
If I hard wire the criteria for field Abc to AAA Or BBB, the query does not
return any data. No quotes.

When the Query criteria for field Abc is set to: [Forms]![Form1]![Texta] and I
select a single value from the list box and format it without quotes and pass
the value to the Texta in the form, the query runs correctly.
If I select a single value from the list box and format it with quotes and pass
the value to the Texta in the form, the query does not return any data. Exactly
like when hard wired.

When I select more than one value from the Listbox and format the output: "AAA"
Or "BBB", the same as when hard wired, the query does not return any data.
There are no leading or trailing spaces in the field.

If I delete the criteria for field Abc and add a field to the query:
T8: [Forms]![Form1]![Text8], it correctly shows "AAA" Or "BBB".

I'm obviously doing something wrong, but I don't have a clue what.
Any suggestions will be greatly appreciated.

Chuck

 

[Jeff Boyce]

Chuck

Any chance the underlying field in the table is a "lookup" data type field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chuck]

Chuck

Any chance the underlying field in the table is a "lookup" data type field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

No. Some of the other fields could be 'lookup" as function of the Abc field
value, but none are.

I have a solution of sorts.
I changed the name of Text8 to Text8a and added a second text box, Text8b.
Then I striped out the field values and set Text8a to the first ListBox value
and Text8b to the second ListBox value.
Then I changed the query where clause from: [Forms]![Form1]![Text8] to:
[Forms]![Form1]![Text8a] Or [Forms]![Form1]![Text8b].
This solution is not generic. It is database specific. The solution is
adequate because the database is used only by me at home.

The code for retrieving the ListBox selections is in the program module.
If I could make Text8a and Text8b global variables, then could I make the query
where clause simply: [Text8a] Or [Text8b]? Then don't use text boxes at all
and assign the selected values to declared global variables LBV1, LBV2 .

Chuck

A97, One Table (Table1), Field to be filtered is a three character text
file
named Abc.
Form1 has a Listbox named ctla and a Textbox named Texta.
Listbox gets values of Abc from a query of Table1 showing field Abc, group
by
and Abc, count.

If I hard wire the criteria for field Abc to AAA, the query runs
correctly.
If I hard wire the criteria for field Abc to "AAA", the query does not
return
any data. Note the quotes.
If I hard wire the criteria for field Abc to "AAA" Or "BBB", the query
runs
correctly. Note the quotes.
If I hard wire the criteria for field Abc to AAA Or BBB, the query does
not
return any data. No quotes.

When the Query criteria for field Abc is set to: [Forms]![Form1]![Texta]
and I
select a single value from the list box and format it without quotes and
pass
the value to the Texta in the form, the query runs correctly.
If I select a single value from the list box and format it with quotes and
pass
the value to the Texta in the form, the query does not return any data.
Exactly
like when hard wired.

When I select more than one value from the Listbox and format the output:
"AAA"
Or "BBB", the same as when hard wired, the query does not return any data.
There are no leading or trailing spaces in the field.

If I delete the criteria for field Abc and add a field to the query:
T8: [Forms]![Form1]![Text8], it correctly shows "AAA" Or "BBB".

I'm obviously doing something wrong, but I don't have a clue what.
Any suggestions will be greatly appreciated.

Chuck

 

[Jeff Boyce]

Chuck

Instead of global variables, just refer to the values in the form's
textboxes, with criteria something like:

Forms!YourFormName!YourFirstTextBoxName Or
Forms!YourFormName!YourSecondTextBoxName

Regards

Jeff Boyce
Microsoft Office/Access MVP

Chuck

Any chance the underlying field in the table is a "lookup" data type
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

No. Some of the other fields could be 'lookup" as function of the Abc
field
value, but none are.

I have a solution of sorts.
I changed the name of Text8 to Text8a and added a second text box, Text8b.
Then I striped out the field values and set Text8a to the first ListBox
value
and Text8b to the second ListBox value.
Then I changed the query where clause from: [Forms]![Form1]![Text8] to:
[Forms]![Form1]![Text8a] Or [Forms]![Form1]![Text8b].
This solution is not generic. It is database specific. The solution is
adequate because the database is used only by me at home.

The code for retrieving the ListBox selections is in the program module.
If I could make Text8a and Text8b global variables, then could I make the
query
where clause simply: [Text8a] Or [Text8b]? Then don't use text boxes at
all
and assign the selected values to declared global variables LBV1, LBV2 .

Chuck

A97, One Table (Table1), Field to be filtered is a three character text
file
named Abc.
Form1 has a Listbox named ctla and a Textbox named Texta.
Listbox gets values of Abc from a query of Table1 showing field Abc,
group
by
and Abc, count.

If I hard wire the criteria for field Abc to AAA, the query runs
correctly.
If I hard wire the criteria for field Abc to "AAA", the query does not
return
any data. Note the quotes.
If I hard wire the criteria for field Abc to "AAA" Or "BBB", the query
runs
correctly. Note the quotes.
If I hard wire the criteria for field Abc to AAA Or BBB, the query does
not
return any data. No quotes.

When the Query criteria for field Abc is set to:
[Forms]![Form1]![Texta]
and I
select a single value from the list box and format it without quotes and
pass
the value to the Texta in the form, the query runs correctly.
If I select a single value from the list box and format it with quotes
and
pass
the value to the Texta in the form, the query does not return any data.
Exactly
like when hard wired.

When I select more than one value from the Listbox and format the
output:
"AAA"
Or "BBB", the same as when hard wired, the query does not return any
data.
There are no leading or trailing spaces in the field.

If I delete the criteria for field Abc and add a field to the query:
T8: [Forms]![Form1]![Text8], it correctly shows "AAA" Or "BBB".

I'm obviously doing something wrong, but I don't have a clue what.
Any suggestions will be greatly appreciated.

Chuck

 

[Chuck]

Chuck

Instead of global variables, just refer to the values in the form's
textboxes, with criteria something like:

Forms!YourFormName!YourFirstTextBoxName Or
Forms!YourFormName!YourSecondTextBoxName

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you Jeff. Works "as advertised".