List boxes to set query parameters

[garywood84]

My database contains information about events running in different areas. I want to make the filtering options for this information as flexible as possible and so want a query where most of the fields can have criteria set, but where they can optionally be left blank and so the data will not be filtered on that field. To do this, I am trying to create form which collects together the parameters required by the user which the query can then pick up.

I want to use list boxes which contain all the entries for a particular field, looked up from the original table (e.g. all the different town names from the town field of all the records). This will allow the user to select the one(s) they want to filter for.

However, using list boxes presents me with the following problems, with which I would appreciate help:

1) How do I refer to a list box from a query and get the correct code created? I could do this where I'm just refering to a text box, but then a text box only contains one value. How do I tell Access to look for any of the towns selected in a list box?

2) There will be options on my form to filter the data by any of the fields of which it is made up. Obviously, the user will not want to filter every field and so I need a way to tell Access that if the user has not made a selection for a particular field, then it should not filter that field when the query runs.

If anyone can help me with these two things I would greatly appreciate it. I've so nearly finished developing a new system and this is the last hurdle I need to overcome!

Thanks in advance,

Gary

 

[GailFialho]

Here's a snippit of a sub I wrote. The user has 3 list boxes to make selections from.
In this first part, the user has NOT selected any specific MODELS or ESNs or Modules.
In the "EsleIf" part, the user has made selections in the listModules and the code will loop through the items in the list and "if selected" will add to the SQL.
I actually use multiple nested if statements to check all three list boxes.

strBuild = ""
strReport = "SELECT CHIT.*, [Part List].* FROM CHIT LEFT JOIN [Part List] ON CHIT.ID = [Part List].ID WHERE "

If Forms!rptWiz0.lstModels.ItemsSelected.Count = 0 Then
If Forms!rptWiz0.lstESNS.ItemsSelected.Count = 0 Then
If Forms!rptWiz0.lstModules.ItemsSelected.Count = 0 Then
GetReportStr = "SELECT CHIT.*, [Part List].* " & _
"FROM ((CHIT LEFT JOIN [Part List] ON CHIT.ID = [Part List].ID) " & _
"LEFT JOIN Model ON CHIT.MODELID = Model.MODELID) LEFT JOIN Access " & _
"ON Model.EngineLineID = Access.ENGINELINEID " & _
"WHERE (((Access.ADMINID)=[Forms]![Main Form]![Combo25])) " & _
"ORDER BY [Part List].Module, [Part List].Sin;"
ElseIf Forms!rptWiz0.lstModules.ItemsSelected.Count > 0 Then
For i = 0 To Forms!rptWiz0!lstModules.ListCount - 1
If Forms!rptWiz0!lstModules.Selected(i) Then
strBuild = strBuild & "(((CHIT.ESNID)='" & Forms!rptWiz0!lstModules.Column(0, i) & "') AND " & _
"(([Part List].Module)='" & Forms!rptWiz0!lstModules.Column(1, i) & " ')) Or "
End If
Next i
strBuild = Left(strBuild, Len(strBuild) - 4)
GetReportStr = strReport & strBuild & " ORDER BY [Part List].Module, [Part List].Sin;"
End If

The sub actually writes the query based on the information selected in a list box.