Listbox items as criteria

[Ray1127]

I have a Microsoft Access 2000 Form that allows the user to select the criteria and run a report. There are 2 items aside from start and end dates that are selected as parameters. A group and subgroup. The subgroup is from a list box that allows the user to select 1 to all of the subgroups. Example:

If the user selects a group in the combo box the list box displays all subgroups for that group. When the group in the combobox is changed the subgroups list changes appropriately. The issue is that in the query for the report I have the criteria set as "In (getlist())" (originally I used a function to get the selected items. but the query came up blank. I put the function in as a column to see what was coming back and it looked ok "060000000245","028288000147" it matched the 2 subgroups that were returned for that group as it should but If I put it into the criteria of the subgroup field I still get nothing.

Here's the function:

Public Function getlist() As String
Dim itm As Variant, intI As Integer
For Each itm In Forms!frmJOMReports.Combo10.ItemsSelected
'check to see if this is the first item selected
If getlist = "" Then
'Add the first item
getlist = Chr(34) & Forms!frmJOMReports.Combo10.ItemData(itm) & Chr(34) & ","
Else
'Add additional Items
getlist = getlist & Chr(34) & Forms!frmJOMReports.Combo10.ItemData(itm) & Chr(34) & ","
End If

Next itm
'Remove the extra comma at the end of the string
getlist = Left(getlist, Len(getlist) - 1)
End Sub

Any help would be appreciated.

 

[SkipVought]

Hi,

'other part of the query...
sSQL = sSQL & "Where [YourField] IN ([b][red]" & getlist() & "[/red][/b])"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

The best you are going to be able to do with getlist is make an SQL String that will run...

dim strSQL as string
strSQL = "Select A.*" & _
     "From A" & _
     "Where A.Field IN(" & getlist() & ");"

The problem is that Access is treating the reuslt from getlist as a single value as opposed to a list. I'm not sure what you would want to do with your SQL string without more infomation. Also if it isn't obivious I used a table name of A and used Field for the name of the field that you want to use as criteria.

 

[Ray1127]

Thanks both of you. I was wondering if that's what was happening but wasn't sure. I'll try you're suggestion's thanks.