That is what I expected. This will take some VBA coding to accomplish.
Multi Select List Boxes have no value of their own. The values are contained
in a collection called ItemsSelected.
What you have to do is build your Where clause programmatically. Here is a
function I use for a similar situation:
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control
Set ctl = Me.Controls(strControl)
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("
With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
BuildWhereCondition = strWhere
End Function
--------------------------------------------
Now, here is an excerpt from the function I use to call it. You will need
something similar. It puts all the criteria together to create one Where
clause:
strWhereNext = BuildWhereCondition("lstBillProdOffering")
strFieldName = "quniUPOReportsPVA.ProjectID "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
'ITM
strWhereNext = BuildWhereCondition("lstITM")
strFieldName = "quniUPOReportsPVA.ITM "
If Len(strWhereNext) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strFieldName & strWhereNext
Else
strWhere = strFieldName & strWhereNext
End If
End If
------------------------
After the above code has completed (there is more in mine because it has 7
list boxes and and option group to inclue), the following, which is still in
the same function, actually creates the query to use. What I have is the
query stored without the WHERE clause. I read the SQL in from a "template"
query, add the WHERE clause, and write it back out to the one I actually use
in the TransferSpreadsheet.
'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_UPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf
If Len(strWhere) > 0 Then
strWhere = "WHERE " & strWhere & "; "
strSQL = Replace(strSQL, ";", strWhere)
End If
Set qdf = dbf.CreateQueryDef("_UPOTemp", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qdf.Name, _
strSaveFileName, True, strRangeName
Dan @BCBS said:
Yes, they are both set for Simple.....
Klatuu said:
Are either of your List Boxes Multi Select?
Dan @BCBS said:
My form has 2 List boxes and a command button that creates a report based on
those 2 choices.
Now I need to produce the same results in a Excel sheet (DoCmd.OutputTo
acOutputQuery, "q_AuditDump", acFormatXLS,...)
I realise I will need a new query. Could you please help me write the SQL on
the query to choose from the 2 list boxes on the form.
The 2 list boxes are "ListArea" and "ListProduct"
The form is called "f_AuditDump"
This is what I have but it returns no data:
WHERE (((tblTrackingData.TR_PRODUCT)=[Forms]![f_AuditDump]![ListProduct])
AND ((tblTrackingData.TR_GBU)=[Forms]![f_AuditDump]![ListArea]));
Help...
