Syntax Error in strSQL

[June7]

Consider:

        '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 &  "'"  & Me!lstStates.ItemData(varItem) & "',"
            Next varItem
            strCriteria = "T11_CrossFunctionalTeam.CFT_Owner IN(" & Left(strCriteria, Len(strCriteria) - 1) & ")"
        Else
            strCriteria = "T11_CrossFunctionalTeam.CFT_Owner LIKE '*'"
        End If

Then build the WHERE:

"WHERE " & strCriteria & " " & _

Also, might want to use an alias field name for NCode_Group([N_Code]) instead of letting Access supply something generic and meaningless like [Expr1].

NCode_Group([N_Code]) AS GrpN

[skydivetom]

June7 -- thank you... VBA still "barks" at me when selecting a value from the listbox. See attached JPG... once I click debug the following line is highlighted in VBA:

qdf.SQL = strSQL

Below is the full VBA:

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 & "'" & Me!lstStates.ItemData(varItem) & "',"
            Next varItem
            strCriteria = "T11_CrossFunctionalTeam.CFT_Owner IN(" & Left(strCriteria, Len(strCriteria) - 1) & ")"
        Else
            strCriteria = "T11_CrossFunctionalTeam.CFT_Owner LIKE '*'"
        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 " & strCriteria & " " & _
                 "ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
        
        
                ' "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 " & 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

Also, "NCode_Group" is an alias. I don't have meaningless [Expr1] in the query.

Thank you in advance for helping me figuring out as to why the query's SQL won'te be regenerated with the qdf.SQL = strSQL line.

[accesstos]

Tom, if you are still alive, follow those steps:

Create a query, in query builder, that returns all the CFTs owners as desired, with the WHERE clause as follows: WHERE 1=1
Save it with the name Q_ALL_CFT_Owners.

Then paste this function in a standard code module:

Public Function SQL4ExportCFTOwners(Optional ByVal strCriteria As String) As String
    Dim db As DAO.Database
    Dim strSQL As String
    
    On Error GoTo ErrH
    Set db = CurrentDb
    'Get the SQL string from the template query.
    strSQL = db.QueryDefs("Q_ALL_CFT_Owners").SQL
    
    If Len(strCriteria) = 0 Then
        'No criteria. Return all records.
    Else
        'Use the input criteria in place of 1=1.
        'Debug.Print "Criteria: '" & strCriteria & "'"
        strSQL = Replace(strSQL, "1=1", strCriteria)
    End If
    'Set the SQL of the actual query that is using as report's row source.
    db.QueryDefs("Q201_CFT_CFT_Ownership_Report_Ncode_Gonzales_RPT").SQL = strSQL
    'All good!
    SQL4ExportCFTOwners= strSQL
ExitHere:
    On Error Resume Next
    Set db = Nothing
    Exit Function
ErrH:
    MsgBox "Error (" & Err & ")" & vbCrLf & vbCrLf & Err.Description, vbExclamation, "Export CTF Owners"
    Debug.Print CurrentProject.Name & "::CFTOwnersSQL Error(" & Err & ")" & vbCrLf & vbTab & " SQL: " & strSQL
    Resume ExitHere
End Function

and use it in your code as follows:

Private Sub ExportCFTOwners()
    'Procedure in the forms's code module. 
    'Can be called form a button click, a listbox double click etc.
    Dim varItem As Variant
    Dim strCriteria As String

    'Loop through the selected items in the list box and build a text string
    With Me!lstStates
        If .ItemsSelected.Count > 0 Then
            For Each varItem In .ItemsSelected
                strCriteria = strCriteria & .ItemData(varItem) & ","
            Next varItem
            strCriteria = "T11_CrossFunctionalTeam.CFT_Owner IN(" & Left(strCriteria, Len(strCriteria) - 1) & ")"
        End If
    End With

   If Len(SQL4ExportCFTOwners(strCriteria)) = 0 Then
        'Failed to build the query. User has informed from the error handler of CFTOwnersSQL().
    Else
        'Success! Show the query and export the report.
        'DoCmd.OpenQuery "Q201_CFT_CFT_Ownership_Report_Ncode_Gonzales_RPT", acViewNormal
        'DoCmd.OpenReport "Report", acViewPreview
        'DoCmd.RunSavedImportExport "Export_Report"
    End If
    
    'Done! Exit proc...

End Sub

Those makes your code simplier and the debugging easier, but, about your query issues, I suspect that the NCode_Group([N_Code]) in GROUP BY clause is the cause of the exceptions. Maybe it needs to create a separate query with the N_Codes and use it with an inner join on the corresponding fields.

I hope that I'm not killing you.

Regards,
John

[davegri]

Need a space here. Without space, Access sees IN as a function.

strCriteria = "T11_CrossFunctionalTeam.CFT_Owner IN (" & Left(strCriteria, Len(strCriteria) - 1) & ")"

[accesstos]

You're right davegri!
Fortunately, in this case, this SQL expression passes in a query definition.

[June7]

Ooops, sorry about the missing space.

And I see now where NCode_Group([N_Code]) does have alias in SELECT clause and alias not needed in GROUP BY.

[pbaldy]

Need a space here. Without space, Access sees IN as a function.

That has not been my experience. Just looked in a db I'm working on and find numerous instances of In(), both hard coded:

strCrit = "Type IN(10,14,17,19)"

and not:

strCNG = "FuelType In(" & strCNG & ")"

All work flawlessly.

[June7]

I also thought I had used without space in code. In query object Access does add space if neglected. But perhaps modifying query with QueryDefs is different. So be safe and include space.

[skydivetom]

This is **killing me**...

So, the original process works flawlessly. That is, in the original process, I identified "owners" (CFT_Owners). There are fewer "owners" than participants.

In this modified process, I need to follow the same principle of choosing a listbox but I need to focus on "Participants". While streamlining may be nice, I am very happy w/ the original process except that the strCriteria appears to causing issues given the (NCodes or participants) use an alias while owner don't use an alias.

Personally, in order to get finish this, I rather use the original process but a modified strCriteria. Is that possible? If so, how?

I don't seem to get the module process working and it results in yet another set of errors... too tired to go through that process from a different angle now.

[pbaldy]

In my case those strings are used for either a wherecondition or setting a recordsource.

[June7]

Of course criteria can be modified. I think your other thread has details.