troubleshooting Question

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

Avatar of Erika Anderson
Erika AndersonFlag for United States of America asked on
Microsoft AccessVBASQL
19 Comments8 Solutions436 ViewsLast Modified:
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;"
ASKER CERTIFIED SOLUTION
Anders Ebro (Microsoft MVP)
Microsoft Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 8 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 8 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros