Problem with Access List Boxes in SQL

[TZyk2003]

Hey everyone. It's good to be on the board.

I have a question. I am trying to take a list box with the values ('A', 'B', 'C') and then run a query based on what is chosen in the list box. I have the list box set to "MultiSelect: Extended" since you can pick more than on letter. The problem arises here:

SELECT DISTINCTROW Customer.CONVERGENT_ACCT_ID, Customer.BILL_ID, Customer.CUSTOMER_NAME, Customer.BILL_PERIOD, Customer.AM_USER_MAINTAINED, Customer.ACCT_MARKING, Customer.HIERARCHY_CUST, Hierarchy.LEVEL_1, Hierarchy.LEVEL_2, Hierarchy.LEVEL_3, Hierarchy.LEVEL_4, Hierarchy.LEVEL_5, BTN.BTN, BTN.HIERARCHY_ID, BTN.ACTIVATION_IND, BTN.SERVICE_PROVIDER, BTN.COMMENTS
FROM (Customer INNER JOIN Hierarchy ON (Customer.BILL_ID = Hierarchy.BILL_ID) AND (Customer.CONVERGENT_ACCT_ID = Hierarchy.CONVERGENT_ACCT_ID)) INNER JOIN BTN ON Hierarchy.HIERARCHY_ID = BTN.HIERARCHY_ID
WHERE (((BTN.ACTIVATION_IND)='A') AND ((BTN.SERVICE_PROVIDER)=[Forms]![Hierarchy Document by Region]![Service_Provider']));

It's that last part: ((BTN.SERVICE_PROVIDER)=[Forms]![Hierarchy Document by Region]![Service_Provider'])); that I need to fix. It works fine if I choose one letter from the list box (only if I set the List Box to MultiSelect: None) but if I chose more than one the query doesn't go through. I have tried putting single and double-quotes around the [Forms]...but no luck.

THANKS for ANY help!

-Tim

 

[jebry]

Hi Tim!

It can't be done this way. The value of a multiselect list box is always null whether none are selected or everything is selected. Are you using this query as the basis of a report or form? If so you can build a criteria string and use it when opening the form/report, just delete the last part of the query. If you are opening the query by itself then change the last part to ((BTN.SERVICE_PROVIDER) IN [Enter Provider List])). Then you can pass your list as a parameter when opening the query.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[TZyk2003]

Ok, thanks for the help!!

Here is what I am doing. I using this query to run a report.

I changed the last part to:

WHERE (((BTN.ACTIVATION_IND)='A') AND ((BTN.SERVICE_PROVIDER) IN ('A', 'B', 'C')));

However, SERVICE_PROVIDER is the name of the list box with A, B, and C in it. When I click on the listbox, I want to select the providers I want to see in the report, then click the "run report" command box.

When using the code above, the providers shown in the report are the ones inside the IN (...) but I want the providers shown that I selected in the listbox SERVICE_PROVIDER.

Does this make sense? THANKS!, this board is great...

-Tim

 

[jebry]

Hi Tim!

Change your SQL to:

SELECT DISTINCTROW Customer.CONVERGENT_ACCT_ID, Customer.BILL_ID, Customer.CUSTOMER_NAME, Customer.BILL_PERIOD, Customer.AM_USER_MAINTAINED, Customer.ACCT_MARKING, Customer.HIERARCHY_CUST, Hierarchy.LEVEL_1, Hierarchy.LEVEL_2, Hierarchy.LEVEL_3, Hierarchy.LEVEL_4, Hierarchy.LEVEL_5, BTN.BTN, BTN.HIERARCHY_ID, BTN.ACTIVATION_IND, BTN.SERVICE_PROVIDER, BTN.COMMENTS
FROM (Customer INNER JOIN Hierarchy ON (Customer.BILL_ID = Hierarchy.BILL_ID) AND (Customer.CONVERGENT_ACCT_ID = Hierarchy.CONVERGENT_ACCT_ID)) INNER JOIN BTN ON Hierarchy.HIERARCHY_ID = BTN.HIERARCHY_ID
WHERE BTN.ACTIVATION_IND='A'

And on the form (I am assuming that you are using a button click to open the form) use the following code in the button's click event:

Dim varRow As Variant
Dim strCriteria As String

strCriteria = ""
For Each varRow in Me!lstServiceProvider.ItemsSelected
If strCriteria = "" Then
strCriteria = "BTN.Service_Provider In ('" & Me!lstServiceProvider.Column(0, varRow) & "'"
Else
strCriteria = strCriteria & ", '" & Me!lstServiceProvider.Column(0, varRow) & "'"
End If
Next varRow

strCriteria = strCriteria & &quot"

DoCmd.OpenReport "YourReport", acViewPreView, , strCriteria

Note, I changed the name of your list box so it doesn't match the name of the field. I strongly suggest that you do this since Access is easily confused.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[TZyk2003]

It worked!

THANKS SO MUCH Jeff. I truly appreciate it!!

-Tim