Multi List Box plus other Form criteria

[jtish]

I have created a form that enables a subquery display and report generation. The outputs are based on the option of a single through multiple criteria inputs (query records based on city, zip code, date range, etc.). I was recently asked to enable the selection of multiple cities, so logically I created a multi list box that presents all the available cities without duplicates in A-Z ascending order. Everything works perfectly when my inputs were single entry qualifiers; however, incorporating the single column multi list box (with Simple multi-selection) is causing me grief. My filter code is essentially a combination of Allen Browne's (for the single value entry across multiple field options) and Baldy's for the multi list box. Any guidance on how to incorporate them all would be much appreciated. Thanks so much@

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim ctl As Control
Dim varItem As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
End If

If Not IsNull(Me.txtfilterZip) Then
strWhere = strWhere & "([Zip] Like ""*" & Me.txtfilterZip & "*"") AND "
End If

If Not IsNull(Me.txtFilterAddKey) Then
strWhere = strWhere & "([AddressL1] Like ""*" & Me.txtFilterAddKey & "*"") AND "
End If

If Not IsNull(Me.cboFilterMinTax) Then
strWhere = strWhere & "([Tax] >= " & Me.cboFilterMinTax & ") AND "
End If

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

Set ctl = Me.txtFilterCity
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[pbaldy]

To incorporate mine, you'd need to include the part that would build an "In" clause. You also don't have a field name. You're building the string:

'abc', 'def'

but the finished product has to look like:

"FieldName In('abc', 'def') AND "

The last part to accommodate the part at the end that will strip off 5 characters.

 

[jtish]

Thanks Paul. I may be in way over my head here but I intentionally omitted that part of the event procedure as it was tied to report generation. In my form, the inputs create the filter to generate a subquery. I then have an event procedure which opens a crosstab query as an xls export based on the filter criteria. So my issue is capturing all the data feeds from 4-5 single entry unbound boxes and one multi-list to create the subquery and crosstab query. Make sense? Thanks again for the prompt response.

 

[pbaldy]

It would need to be modified, but you still have to build a valid string. Try something like this, adjusting the field name of course:

  Set ctl = Me.txtFilterCity
  If ctl.ItemsSelected.Count > 0 Then
    strWhere = strWhere & "FieldName In("
    For Each varItem In ctl.ItemsSelected
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    strWhere = strWhere & ") AND "
  End If

If that doesn't work, this will help us find the problem:

http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

Oh, and add a line to strip off the trailing comma that the loop will leave.

 

[jtish]

That did the trick Paul. Thank you so much for your support (and patience).

Jeff

 

[pbaldy]

Happy to help Jeff.

 

[jtish]

Paul's suggestions worked wonders for the multi-field filtering (including a multi list box) and display on the form. I have a command button that should take the results from the form display and convert it via a crosstab query into an xls file. So my new question is what is the proper crosstab criteria (or SQL) input to capture the string from the multi list box (txtFilterCity)? I have tried multiple variations based on what you posted above but without success. I just left the below example in since it produced data...but includes Cities that I did not select in the multi list. Here is what I have and it is not recognizing the multi list box selections:

PARAMETERS [Forms].[frmClientSearch].[txtFilterMainName] Text ( 255 ), [Forms].[frmClientSearch].[txtFilterCity] Text ( 255 ), [Forms].[frmClientSearch].[txtStartDate] DateTime, [Forms].[frmClientSearch].[txtEndDate] DateTime, [Forms].[frmClientSearch].[txtfilterZip] Text ( 255 ), [Forms].[frmClientSearch].[txtFilterAddKey] Text ( 255 ), [Forms].[frmClientSearch].[cboFilterMinTax] Currency;
TRANSFORM Sum([Tax]/0.14) AS [Alcohol Sales]
SELECT tblClient.MainName AS Restaurant, tblClient.AddressL1 AS Address, tblClient.City, tblClient.Zip, Sum([Tax]/0.14) AS [Total of Alcohol Sales]
FROM tblClient
WHERE (((tblClient.MainName) Like "*" & [Forms].[frmClientSearch].[txtFilterMainName] & "*") AND ((tblClient.City) Like "*" & [Forms].[frmClientSearch].[txtFilterCity] & "*") AND ((tblClient.Zip) Like "*" & [Forms].[frmClientSearch].[txtfilterZip] & "*") AND ((tblClient.Tax)>[Forms].[frmClientSearch].[cboFilterMinTax]) AND ((tblClient.AddressL1) Like "*" & [Forms].[frmClientSearch].[txtFilterAddKey] & "*") AND ((tblClient.EnteredOn) Between [Forms].[frmClientSearch].[txtStartDate] And [Forms].[frmClientSearch].[txtEndDate]))
GROUP BY tblClient.MainName, tblClient.AddressL1, tblClient.City, tblClient.Zip
ORDER BY tblClient.MainName, tblClient.EnteredOn
PIVOT tblClient.EnteredOn;

Can't thank you (or anyone else who takes time out of their day to look at my Access challenges) enough.

Jeff

 

[jtish]

CROSSTAB QUERY: Multi List Box plus other Form criteria

Per the post above, what would the proper SQL "Where" criteria look like to pull the multi-list box selections from the form into a crosstab query? Is there anyway to automatically populate the selections from the list box for use with the IN clause? Any help would be much appreciated.

Cheers,

Jeff

 

[Alansidman]

CrossPosting: http://www.accessforums.net/queries/crosstab-query-based-multi-list-form-selection-25522.html

Please read:http://www.excelguru.ca/content.php?184

 

[jtish]

Thanks for the education re: cross-posting Alan. Definitely no intention to upset the readers. I was guilty of looking to maximize the audience after a delay in feedback. My apologies. I iwll update my other forum post with the URL and direct responses back here. Thanks for setting me down the proper path.

Jeff