We help IT Professionals succeed at work.
Get Started

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

412 Views
Last Modified: 2017-11-12
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

Comment
Watch Question
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 8 Answers and 19 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE