Avatar of Erika Anderson
Erika Anderson
Flag for United States of America

asked on 

Proper formatting on variable in SQL string within VBA/MS Access

Good morning - I have done a lot of testing and trying to teach myself on this, but I've not been successful. I have an ODBC connection to PeopleSoft and am attempting to put the PeopleSoft SQL into VBA. I am successful at reproducing the same output in Access when I do not declare variables, but am getting an error, "An unexpected token "Dept" was found following "DEPT". Expected tokens may include: ":", SQLSTATE=42601.

I have taken just this variable for DeptID (DEPT) and changed the data type in the 3 places I am using the value - in a table (AcctPeriod_tbl) that is updated with values from a user form (Accounting Period, DeptID, Business Unit, Fiscal Year - DeptID is a Short Text data type here), in PeopleSoft - it is Char10 here, and in VBA - as String shown below. I think I need to maybe add double quotes, or maybe a single quote after the equal sign, and perhaps ampersands around the variable but looking for some guidance. When I changed to "AND A.DEPTID = '" & DEPT & _ the error changes to "The string constant beginning with "" does not have an ending string delimiter. SQLSTATE=42603(#-10). I can provide the rest of the variables and code if needed, as I'm trying to first isolate this one.

Private Sub erikatesting_Click()
    Dim dbs As DAO.Database
    Dim myQueryDef As DAO.QueryDef
    Dim strSql As String
    Dim rst As DAO.Recordset
    Dim DEPT As String

    Set dbs = CurrentDb()
    
    Set rst = dbs.OpenRecordset("AcctPeriod_tbl")
    DEPT = rst!DeptID

       strSql = "SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT PRODUCT, FISCAL_YEAR AS YEAR, " & _
         "FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
         "WHERE ( A.BUSINESS_UNIT = ('WDBIL') " & _ 
         "AND A.DEPTID = DEPT & _
         " GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR " & _
         " ORDER BY 3, 1 WITH UR;"

Open in new window

Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Erika Anderson

8/22/2022 - Mon