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;"