Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Syntax Error in strSQL

    Experts:

    I have a form with listbox... once a value is selected from the listbox, it's passing it into a query (criteria). The code for my sample db is a follows:

    Code:
            strSQL = "SELECT * FROM T00_SourceData " & _
                         "WHERE " & strCriteria & ";"
    I now tried to replicate the process into my actual database. At this time, I'm getting a syntax error. Code is shown below:

    Code:
            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;"
    The problem -- I believe -- lies in the 2nd to last statement with the WHERE clause... where's my mistake preventing me from executing the SQL?

    Thank you,


    EEH

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This should help:

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

    What does the variable contain? You're using it differently.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    pbaldy:

    Thank you... that Debug.Print statement is definitely helpful. Still, I'm not succeeding as passing the listbox value into the criteria (where clause). Any additional thoughts? Below is the full VBA.


    Code:
            '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 & "T00_SourceData.State = " & Chr(34) _
                                  '& Me!lstStates.ItemData(varItem) & Chr(34) & "OR "
                
                
                
                    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 * FROM T00_SourceData " & _
                     '"WHERE " & strCriteria & ";"
            
            
            
            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

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you didn't give me the contents of the criteria variable or the full finished SQL. At a guess, the variable contains something like

    State = "X" OR State = "Y"

    but you're dropping it in after the field Owner. The debug should show that if I'm right. The first would work because it wasn't preceded by a field name. In other words you probably have

    WHERE Owner = State = "X" OR State = "Y"

    which doesn't make sense.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    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

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I was after the results of this line:

    Debug.Print strSQL

    That will show us what the criteria variable contained and how it got integrated into the SQL. Often just seeing this will make the answer obvious.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    The immediate window outputs the following:

    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_Junct
    ionTable_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)=T11_CrossFun ctionalTeam.CFT_Owner = "N042")) ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;

    There's some repetion in the where clause... what should I remove to not get what I perceive to be the error in **WHERE (((T11_CrossFunctionalTeam.CFT_Owner)=T11_CrossFun ctionalTeam.CFT_Owner = "N042"))**?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That is certainly the problem. You have to take it out of the listbox code or the final building of the SQL. Since your listbox might return multiple items, you need it there. Thus take it out of the final bit:

    "WHERE " & strCriteria & " " & _

    note I took out the extraneous parentheses that the QBE likes to add.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Paul -- thank you... the SQL seems to be correct now; however, the VBA results in a run-time error. Line "qdf.SQL = strSQL" is highlighted.

    See attached error message.

    What else might be missing / is wrong w/ the code?
    Attached Thumbnails Attached Thumbnails RunTimeError.JPG  

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is NCode_Group([N_Code])? Is that really a field name? Or is NCode_Group() a VBA custom function?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm on a mobile right now, but that bit before where is odd. What's that supposed to be? I'd create a query in the design grid and then copy the SQL, adding the necessary line breaks and such.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    N_Code is a field name. NCode_Group refers to a conversion module allowing me to aggregate N-codes into a larger summary code within the hierarchy.

    For example, I may have N04221 and N042223... these sub-orgs need to be aggregated under the larger NCode_Group = N04

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Paul... thank you... I understand... hopefully I get it solved by tomorrow. If not, I'm certainly open to additional advice. Thanks again.

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    The strCriteria still does not pass the selected value into field [CFT_Owner]... very confusing...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-15-2019, 10:20 AM
  2. Replies: 6
    Last Post: 01-29-2014, 08:03 AM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  5. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums