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