Criteria as multi-select list box

[MarkW]

Hello... I've read through several related topics here, but can't find a way to make this work.

I have a multi-select (Branch List) list box on my form (Main Menu) in which I need to create a string based on the selected branches and use as the criteria for my query.

I have the following code to build the string in a module:

Public Function basBuildWhereString() As String

Dim varItem As Variant
Dim intI As Integer

If Forms("Main Menu")![Branch List].ItemsSelected.Count > 0 Then
intI = 1
For Each varItem In Forms("Main Menu")![Branch List].ItemsSelected
basBuildWhereString = basBuildWhereString & """" & Forms("Main Menu")![Branch List].ItemData(varItem) & """"
If Forms("Main Menu")![Branch List].ItemsSelected.Count > intI Then
basBuildWhereString = basBuildWhereString & " Or "
intI = intI + 1
End If
Next varItem
End If
MsgBox (basBuildWhereString)
End Function

I have this function name in the criteria field of my query. The string is built correctly, as verified by the MsgBox function, however the query is not filtered per this string.

Any suggestions?

 

[David R]

I've never used that structure as a Public Function, but it works fine as an internal code snippet.

However in your form code, where you have
Me.Filter = basBuildWhereString or whatever, put a breakpoint (click in the left margin and a red dot should appear). Run your code, build a filter, try it and your code should stop and show you that screen. Debug your basBuildWhereString to see if it's being used correctly there as well.

 

[MarkW]

Hmmm... I'm more confused now. The only code I have on the form is the code that opens the query (DoCmd.OpenQuery) attached to the event procedure of a command button.

Maybe I'm doing this wrong. I'm trying to have the button on my form open the query, while the query contains a function in the criteria field which is built from selections on the form's list box.

Should I pass the string from the form to the query instead?

 

[MarkW]

You're right Pat... the criteria string is misinterpreted by the query. I was able to build the SQL string successly for a simple query in VBA, however...

The existing query I'm trying to apply this to has a very long SQL (20 columns from my table, with sums and group by statements). It's impossible for me rebuild the SQL because a string can't exceed 255 chars. I need to reference just the WHERE clause. With a report you can simply pass the WHERE clause as an argument in the DoCmd.OpenReport statement.

Maybe I'm making this too complex... Is there an easier way to do this that I'm overlooking?

 

[Rich]

Can't you put the calculations and grouping on the report.

 

[MarkW]

The goal here is to export the query to Excel once filtered. This doesn't work so well in report format.

 

[MarkW]

Thanks for all the help everyone. I've solved my problem by inserting the Where string into the query's filter instead of the criteria field as follows:

DoCmd.OpenQuery stDocName
DoCmd.ApplyFilter stDocName, WhereStr
DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, , True

Thanks again all!