Paul:
I apologize for not including the full VBA... here it is:
Code:
Option Compare Database
Option Explicit
Private Sub cmdExportReport_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("Q201_CFT_CFT_Ownership_Report_Ncode_Gonzales_RPT")
'Loop through the selected items in the list box and build a text string
If Me!lstStates.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstStates.ItemsSelected
strCriteria = strCriteria & "T11_CrossFunctionalTeam.CFT_Owner = " & Chr(34) _
& Me!lstStates.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "T11_CrossFunctionalTeam.CFT_Owner '*'"
End If
'Build the new SQL statement incorporating the string
strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
"T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, Count(T00_JunctionTable_BCFT.BilletIDfk) AS NoParticipants, " & _
"T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
"T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, " & _
"NCode_Group([N_Code]) AS NCode_Group FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
"RIGHT JOIN ((T99_SortingCFTOwner INNER JOIN T11_CrossFunctionalTeam ON T99_SortingCFTOwner.CFTOwner = T11_CrossFunctionalTeam.CFT_Owner) INNER JOIN (T01_Organization " & _
"RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) INNER JOIN T00_JunctionTable_BCFT " & _
"ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
"ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk " & _
"GROUP BY T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
"T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, " & _
"T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, " & _
"T01_StaffMembers.Mil_PRD, NCode_Group([N_Code]) " & _
"WHERE (((T11_CrossFunctionalTeam.CFT_Owner)=" & strCriteria & ")) " & _
"ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
Debug.Print strSQL
'Apply the new SQL statement to the query
qdf.SQL = strSQL
'Open the query
'DoCmd.OpenQuery "Q01_Query"
'DoCmd.OpenReport "Report", acViewPreview
'Execute export routine (onto C:drive)
DoCmd.RunSavedImportExport "Export_Report"
MsgBox "The report was stored at the following location: C:\Users\dellc\", vbInformation, "Information"
'Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
So, the listbox includes org codes... whatever org code is selected in the listbox, I then want to pass into the criteria.
Thanks,
EEH