If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Use List Box to select a paramter for a query
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 |
#2
|
|||
|
|||
Use List Box to select a paramter for a query
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 |
#3
|
|||
|
|||
Use List Box to select a paramter for a query
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 |
#4
|
|||
|
|||
Use List Box to select a paramter for a query
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 Gary Walter wrote: 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) |
#5
|
|||
|
|||
Use List Box to select a paramter for a query
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 Gary Walter wrote: 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) |
#6
|
|||
|
|||
Use List Box to select a paramter for a query
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. "jeh" wrote in message ups.com... 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 Gary Walter wrote: 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) |
Thread Tools | |
Display Modes | |
|
|