Use List Box to select a paramter for a query

[jeh]

For the first time I'm trying to base a query on a user selection via a
list box. My simple test db has one table, two forms and two queries.

Tbl_1 contains four records, two fields:
1 abc
2 def
3 ghi
4 jkl

Frm_1 contains a single text box "Text1" in which I can enter anything
I like.
Qry_1 has the two fields from Tbl_1 with the criterion for the second
field being
Like "*" & [Forms]Frm_1![Text1] & "*"

The SQL is:
SELECT Tbl_1.Field1, Tbl_1.Field2
FROM Tbl_1
GROUP BY Tbl_1.Field1, Tbl_1.Field2
HAVING (((Tbl_1.Field2) Like "*" & [Forms]![Frm_1]![Text1] & "*"));

Opening Frm_1 and typing "b" produces "1 abc" in Qry_1, exactly as
expected.

Frm_2 contains a single list box "List0" based on Tbl_1 with the key
field hidden. The correct four fields show in the list box when Frm_2
is run.
Qry_2 is the same as Qry_1 but with Field 2 criterion changed to
Like [Forms]Frm_2![List0]

Qry_2's SQL is
SELECT Tbl_1.Field1, Tbl_1.Field2
FROM Tbl_1
WHERE (((Tbl_1.Field2) Like [Forms]![Frm_2]![List0]));

No matter what I select in Frm_2, running Qry_2 produces a null result.
(Frm_2 is open at the time)

This is so similar to Joanna's query that John Vinson answered a few
minutes ago that I must have missed something blindingly obvious. Can
anyone suggest what I might have done wrong?

TIA

John

 

[Gary Walter]

In all probabibility the "bound" column
of your listbox is on your first number field.

try for criteria

Forms!Frm_2!List0.Column(1)

which will reference the *second* column.

I see no reason to use "LIKE" ....

(or change Bound Property of
List0 to second column)

"jeh"wrote:

 

[Gary Walter]

it is also a likely "probability"....

"Gary Walter" typed crappily:

In all probabibility the "bound" column
of your listbox is on your first number field.

try for criteria

Forms!Frm_2!List0.Column(1)

which will reference the *second* column.

I see no reason to use "LIKE" ....

(or change Bound Property of
List0 to second column)

"jeh"wrote:

For the first time I'm trying to base a query on a user selection via a
list box. My simple test db has one table, two forms and two queries.

Tbl_1 contains four records, two fields:
1 abc
2 def
3 ghi
4 jkl

Frm_1 contains a single text box "Text1" in which I can enter anything
I like.
Qry_1 has the two fields from Tbl_1 with the criterion for the second
field being
Like "*" & [Forms]Frm_1![Text1] & "*"

The SQL is:
SELECT Tbl_1.Field1, Tbl_1.Field2
FROM Tbl_1
GROUP BY Tbl_1.Field1, Tbl_1.Field2
HAVING (((Tbl_1.Field2) Like "*" & [Forms]![Frm_1]![Text1] & "*"));

Opening Frm_1 and typing "b" produces "1 abc" in Qry_1, exactly as
expected.

Frm_2 contains a single list box "List0" based on Tbl_1 with the key
field hidden. The correct four fields show in the list box when Frm_2
is run.
Qry_2 is the same as Qry_1 but with Field 2 criterion changed to
Like [Forms]Frm_2![List0]

Qry_2's SQL is
SELECT Tbl_1.Field1, Tbl_1.Field2
FROM Tbl_1
WHERE (((Tbl_1.Field2) Like [Forms]![Frm_2]![List0]));

No matter what I select in Frm_2, running Qry_2 produces a null result.
(Frm_2 is open at the time)

This is so similar to Joanna's query that John Vinson answered a few
minutes ago that I must have missed something blindingly obvious. Can
anyone suggest what I might have done wrong?

TIA

John

 

[jeh]

Gary:

Thanks for the help. That was exactly the problem.

Maybe I mis-typed, but my Access 2000 complained that the ".Column(1)"
construct produced an illegal criterion. The alternative, changing
List0's Bound Column property from 1 to 2 fixed the problem.

It would have been quite a while before I'd picked that one up myself.
Access bites me most times I try something I've not done before!

Cheers

John

 

[jeh]

Gary:

Thanks for the help. That was exactly the problem.

Maybe I mis-typed, but my Access 2000 complained that the ".Column(1)"
construct produced an illegal criterion. The alternative, changing
List0's Bound Column property from 1 to 2 fixed the problem.

It would have been quite a while before I'd picked that one up myself.
Access bites me most times I try something I've not done before!

Cheers

John

 

[John Spencer]

As far as I know, you can't refer to columns of comboboxes and listboxes in
a query. The expression engine doesn't seem to handle them. What you can
do is use a custom vba function or assign the needed value to another
control (Visible property set to no) and use that control.