Multiple selections in a form to run a query

[jinman1883]

I'd like a front-end form which gives 3 choices/sections which have selectable choices:

BY STATUS:
Current
Historical


BY GEOGRAPHY TYPE:
Administrative
Electoral
Health
Statistical Building Block
Census
Economic
Crime
Education
European
Other


BY COUNTRY:
England
Wales
Scotland
Northern Ireland


I'd like to be able to select any combination and as many choices from the three sections and use those selections in a query to interogate a "master" table.

In the past I have used many different queries and macros to cover each possible selection. However this time there are alot more choices and possible combinations and so the number of macros and queries would run into the hundreds.

Is there a way to create a form that would input the query criteria into query after the selections have been made so it filters the table and the results shown in a new table?

Thanks for your time

Matt

 

[dcx693]

Customizing Access Parameter Queries

 

[jinman1883]

That would only seem to work by selecting one criteria from each section. What would I use if I wanted to be able to multi select the criteria?

 

[jinman1883]

No Worries, I have found this http://www.fontstuff.com/access/acctut11.htm which may help me some of the way

 

[dcx693]

If you're going to use multiselect listboxes, then you'll want to read this: Use Multi-Select List boxes as query parameters

You can no longer use a parameter query and reference the form controls from your query. You'll need to build a query on the fly.

 

[jinman1883]

So far, I have my 2 multi select list boxes and I want to use these as my selection criteria in one query and run the query through one control button.

This is as far as I have got

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("QryGazetteer")
strCriteria = " "
strCriteria1 = " "
For Each varItem In Me!LstStatus.ItemsSelected
strCriteria = strCriteria & ",'" & Me!LstStatus.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!LstGeog.ItemsSelected
strCriteria1 = strCriteria1 & ",'" & Me!LstGeog.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)
strSQL = "SELECT * FROM Gazetteer " & _
"WHERE Gazetteer.Status IN(" & strCriteria & ") " & _
"AND Gazetteer.Geographytype IN(" & strCriteria1 & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "QryGazetteer"
Set db = Nothing
Set qdf = Nothing
End Sub


but I get a Run-time error 3075.

'Syntax error (missing operator) in query expression 'Gazetteer.Status IN(,'Current') AND Gazetteer.Geographytype IN(,'Census,'Economic')'

Please Help!

 

[JSaumya]

Multi-select list box - syntax error

jinman1883,

Sorry not to have a response to your email from March, but I'm having a similar problem and thought maybe if you figured yours out, you'd have an idea for mine.

I've got my code for a multi-select list box to loop through the items selected and build a text string (on which to base my query).

The items in the list box are numbers.

When I run the code, I'm getting Compile Error: Expected: End of Statement.
It says I messed up in my For Each statement, but I can't see my error!

Up to the loop, I've got:

Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qrySelectTract")

' Loop through the selected items in the list box and build a text string
For Each varItem In Me!lstTract.ItemsSelected
strCriteria = strCriteria & Me!lstTract.ItemData(varItem) & " Or [Tract]="
Next varItem


When I use the debugger, it highlights strCriteria (the first instance of it in the line above) and says it's expecting an end of statement.

Can anybody see what's wrong?


Thanks very much!

 

[rajeshhb]

Remove Space from str definition

Looks like when you defined the string you put as space in there. So when you cut the first chaaracter off, you are really removing the space and not the comma as you intended. So you get the syntax error since the comma is still present.