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

Erika AndersonAccounting SystemsAsked: 2017-11-10

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

Here is the complete code if it helps:

Private Sub erikatesting_Click()
    Dim dbs As DAO.Database
    Dim myQueryDef As DAO.QueryDef
    Dim strSql As String
    Dim rst As DAO.Recordset
    Dim AP As Integer
    Dim FY As Integer
    Dim AC As String
    Dim DEPT As String
    Dim PR As String
    Dim BU As String
      
    
    Set dbs = CurrentDb()
    
    Set rst = dbs.OpenRecordset("AcctPeriod_tbl")
    AP = rst!EndAcctPeriod
    FY = rst!FiscYear
    AC = rst!AccountID
    DEPT = rst!DeptID
    PR = rst!Product
    BU = rst!BusUnit
        
        
    strSql = "SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, SUM(DECIMAL(CASE WHEN ACCOUNTING_PERIOD = AP THEN POSTED_TOTAL_AMT " & _
             "ELSE 0 END,19, 2)),SUM(DECIMAL(CASE WHEN ACCOUNTING_PERIOD <= AP THEN POSTED_TOTAL_AMT ELSE 0 END + (CASE WHEN BUSINESS_UNIT = ('WDUSA') THEN CASE WHEN AP >= 9 THEN CASE WHEN ACCOUNTING_PERIOD IN (998,909) THEN POSTED_TOTAL_AMT " & _
             "ELSE 0 END ELSE 0 END ELSE 0 END) + (CASE WHEN BUSINESS_UNIT = ('WDUSA') THEN CASE WHEN AP >= 11  THEN CASE WHEN ACCOUNTING_PERIOD = 904 THEN POSTED_TOTAL_AMT " & _
             "ELSE 0 END ELSE 0 END ELSE 0 END) + (CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN CASE WHEN AP >= 4 THEN CASE WHEN ACCOUNTING_PERIOD = 904 THEN POSTED_TOTAL_AMT " & _
             "ELSE 0 END ELSE 0 END ELSE 0 END) + (CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN CASE WHEN AP >= 7 THEN CASE WHEN ACCOUNTING_PERIOD = 907 THEN POSTED_TOTAL_AMT ELSE 0 End ELSE 0 End ELSE 0 END)+(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN " & _
             "CASE WHEN AP >= 10 THEN CASE WHEN ACCOUNTING_PERIOD = 910 THEN POSTED_TOTAL_AMT ELSE 0 End ELSE 0 End ELSE 0 END)+(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN CASE WHEN AP >= 12 THEN CASE WHEN ACCOUNTING_PERIOD = 912 THEN " & _
             "POSTED_TOTAL_AMT ELSE 0 END ELSE 0 END ELSE 0 END),19, 2)) " & _
             "FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
             "WHERE ( A.BUSINESS_UNIT = BU " & _
             "AND A.LEDGER = ('ACTUALS') " & _
             "AND A.ACCOUNT = B.ACCOUNT " & _
             "AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID " & _
             "AND B.ACCOUNT = B_ED.ACCOUNT " & _
             "AND B_ED.EFFDT <= CURRENT DATE) " & _
             "AND A.FISCAL_YEAR = FY " & _
             "AND A.ACCOUNT = AC " & _
             "AND A.DEPTID = '" & DEPT & _
             "AND A.PRODUCT = PR " & _
             "AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))) " & _
             " GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR " & _
             " ORDER BY 3, 1 WITH UR;"

Open in new window

End Sub

LVL 12

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented: 2017-11-10

Whenever you are dealing with raw SQL and it is proving troublesome, its a good idea to output it to the debug window, then take it from there, and try to paste it into the query editor. This can usually help you resolve errors quickly.
I am having trouble making sense of your query though. It seems to end with a "with" statement which seems wrong. Furthermore, you are sorting by 2 constants, namely 3 and 1, but maybe that is a Peoplesoft special syntax..

In regards to dept you are missing a single ampersand at the end of line 16(original post) line 41 (updated post).

AND A.DEPTID = '" & DEPT & "'" & _

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 41

als315Commented: 2017-11-10

In Access you should exactly follow type of field in SQL statement
If A.DEPTID is numeric, use:
"AND A.DEPTID = " & DEPT & _
if it is string, use quotes:
"AND A.DEPTID = '" & DEPT & "'" &_
or
"AND A.DEPTID = " & chr(34) & DEPT & chr(34) &_

LVL 42

ste5anSenior DeveloperCommented: 2017-11-10

The others already pointed out the concrete error. To avoid it, I prefer a more C-style, formalized approach using placeholders..

Option Compare Database
Option Explicit

Private Sub erikatesting_Click()
    
  Const SQL_SELECT As String = _
    "SELECT *{CRLF} " & _
    "FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B{CRLF} " & _
    "WHERE A.BUSINESS_UNIT = {0}{CRLF} " & _
    "AND A.LEDGER = 'ACTUALS'{CRLF} " & _
    "AND B.ACCOUNT = B_ED.ACCOUNT{CRLF} " & _
    "AND A.FISCAL_YEAR = {1}{CRLF} " & _
    "AND A.ACCOUNT = {2}{CRLF} " & _
    "AND A.DEPTID = {3}{CRLF} " & _
    "AND A.PRODUCT = {4};"
    
    Dim strSql As String
    Dim FY As Integer
    Dim AC As String
    Dim DEPT As String
    Dim PR As String
    Dim BU As String
    
    FY = -2000
    AC = "<AccountID>"
    DEPT = "<DeptID>"
    PR = "<Product>"
    BU = "<BusUnit>"
        
    Debug.Print FormatStr(SQL_SELECT, SqlQuote(BU), FY, SqlQuote(AC), SqlQuote(DEPT), SqlQuote(PR))

End Sub

Public Function FormatStr(AString As String, ParamArray AValues() As Variant) As String

  Dim Count As Long
  Dim Result As String
  
  Result = AString 
  For Count = 0 To UBound(AValues())
    Result = Replace(Result, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count
  
  Result = Replace(Result, "{CRLF}", vbCrLf)
  FormatStr = Result

End Function

Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Open in new window

gives you

SELECT *
 FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B
 WHERE A.BUSINESS_UNIT = '<BusUnit>'
 AND A.LEDGER = 'ACTUALS'
 AND B.ACCOUNT = B_ED.ACCOUNT
 AND A.FISCAL_YEAR = -2000
 AND A.ACCOUNT = '<AccountID>'
 AND A.DEPTID = '<DeptID>'
 AND A.PRODUCT = '<Product>';

Open in new window

The {CRLF} is a pretty-pring/debug helper and can be removed.

LVL 56

Gustav BrockCIOCommented: 2017-11-10

The real fun begins if you can have Null values to insert.

You may take advantage of my CSql function which I have posted several times and also here.

/gustav

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

I actually copied the WITH UR; part from someone else's code in the office, but I removed and still getting unexpected token. That ran before I declared variables. Also when I did not declare variables and used actual values (AND A.DEPTID LIKE '%'). The 3, 1 also works with actual values and variables, but I see what you mean and tried to replace 3 with DEPT and 1 with PRODUCT and still getting unexpected token so I removed ORDER BY completely for now. I believe these are the columns from PeopleSoft.

I tried the next suggestion -

"AND A.DEPTID = '" & DEPT & "'" &_ -------> I fixed formatting on other variables; get a different error "An unexpected toke ")" was found following ")". Expected tokens may include: "AND OR HAVING GROUP INTERSECT ORDER FETCH EXCEPT UNION FOR". SQLSTATE=42601(#-104)

I tried the next suggestion -

"AND A.DEPTID = " & chr(34) & DEPT & chr(34) & _

But I'm having an issue translating FISCAL_YEAR from PeopleSoft set as Num(4.0) -----> tried Num(4) and Nm(4), compile error. Tried " & Int(4) & FY & Int(4) & _ (Came and checked created query in access and show A.FISCAL_YEAR = 420174 instead of 2017.

So here is what I have and then what shows under SQL query design within Access. Error is still "An unexpected token "," was found following ".". Expected tokens may include: ",FROM INTO".

VBA:

  strsql = "SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, " & _
             "FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
             "WHERE ( A.BUSINESS_UNIT = ('WDBIL') " & _
             "AND A.LEDGER = ('ACTUALS') " & _
             "AND A.ACCOUNT = B.ACCOUNT " & _
             "AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID " & _
             "AND B.ACCOUNT = B_ED.ACCOUNT " & _
             "AND B_ED.EFFDT <= CURRENT DATE) " & _
             "AND A.FISCAL_YEAR = " & Int(4) & FY & Int(4) & _
             "AND A.ACCOUNT = " & Chr(10) & AC & Chr(10) & _
             " AND A.DEPTID = " & Chr(10) & DEPT & Chr(10) & _
             " AND A.PRODUCT = " & Chr(10) & PR & Chr(10) & _
             " AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))) " & _
             " GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR;"

Open in new window

SQL Query design results:

SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B WHERE ( A.BUSINESS_UNIT = ('WDBIL') AND A.LEDGER = ('ACTUALS') AND A.ACCOUNT = B.ACCOUNT AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ACCOUNT = B_ED.ACCOUNT AND B_ED.EFFDT <= CURRENT DATE) AND A.FISCAL_YEAR = 420174AND A.ACCOUNT =
116050 AND A.DEPTID = % AND A.PRODUCT = % AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))) GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR;

Wondering now if it's the ('WDBIL') or ('ACTUALS') as the other strings (DEPTID, PRODUCT) do not have parenthesis or quote.

LVL 41

als315Commented: 2017-11-10

Num 4.0 for year should mean 2017, for example
remove all except number:
A.FISCAL_YEAR = " & FY & _
If your value is stored as string, use quotes around this number (I hope FY is 4 digit year)
PS.
I see, it is short text, so:
A.FISCAL_YEAR = " & chr(34) & FY & chr(34) & _

LVL 12

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented: 2017-11-10

Are you executing the SQL against current db, or by using a passthrough?

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

I am attempting both, Anders. When I do a passthrough the sql below, I get expected results. When I attempt to do against + variables, I am stuck.

As passthrough and working as expected (but with no variables):

ELSE 0 
END)+ 
(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN 
CASE WHEN 10 >= 7 THEN 
CASE WHEN ACCOUNTING_PERIOD = 907 THEN 
POSTED_TOTAL_AMT 
         ELSE 0 
         END 
    ELSE 0 
    END 
ELSE 0 
END)+ 
(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN 
CASE WHEN 10 >= 10 THEN 
CASE WHEN ACCOUNTING_PERIOD = 910 THEN 
POSTED_TOTAL_AMT 
          ELSE 0 
          END 
   ELSE 0 
   END 
ELSE 0 
END)+ 
(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN 
CASE WHEN 10 >= 12 THEN 
CASE WHEN ACCOUNTING_PERIOD = 912 THEN 
POSTED_TOTAL_AMT 
         ELSE 0 
         END 
   ELSE 0 
   END 
ELSE 0 
END),19, 2)) 
FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B 
WHERE ( A.BUSINESS_UNIT = ('WDBIL') 
AND A.LEDGER = ('ACTUALS') 
AND A.ACCOUNT = B.ACCOUNT 
AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID 
AND B.ACCOUNT = B_ED.ACCOUNT 
AND B_ED.EFFDT <= CURRENT DATE) 
AND B.SETID = ('WDUSA') 
AND A.FISCAL_YEAR = 2017
AND A.ACCOUNT = ('116050')
AND A.DEPTID LIKE '%'
AND ( A.ACCOUNTING_PERIOD <= 10 OR A.ACCOUNTING_PERIOD IN (998,909,904,907,910,912))) 
GROUP BY  A.BUSINESS_UNIT,  A.ACCOUNT,  B.DESCR, A.PRODUCT,  A.DEPTID,  A.FISCAL_YEAR 
ORDER BY 1, 2, 4 WITH UR;

Open in new window

LVL 12

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented: 2017-11-10

I see. Now you can define or modify the sql of a passthrough before executing it. I think the root problem here is that there are differences in syntax between Access SQL and whichever database is used by peoplesoft. E.g. access does not have a case statement (You can use IIF statements, but that can get nasty quickly if nested).

So I think you should create your sql, then assign it to a passthrough query object (either pre-created, or created on the fly), and then execute that.

Currentdb.QueryDefs("myPassThroughQuery").SQL=strSQL

Open in new window

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

Yes, the case statements are the main reason I was looking to make this happen because I've gotten it to run with hardcoded values for Accounting Period, Fiscal Year...not sure how to modify the variables? Do you mean through joining my user input table to the passthrough and transforming the case statements into IIF? I've been looking at this and testing for so many hours.

Let's say my passthrough is PSFSP8_PS_Ledger. I want to tie it to the values the user inputs for Account, FiscYear, AcctPeriod on the form that I have updating to table AcctPeriod_tbl. I can basically use those 3 and hard code Business Unit as WDBIL, Product as wildcard % and DeptID as %.

I've created a new query from the passthrough and called it PS_Ledger (so that I can look at the design of SQL because I can't when it's a table). I've put the criteria for LEDGER as Actuals. Now I have to figure out the CASE part and variables. I'm back to square one I feel like. The part that I'm just brain dead at this point is the variables. The query in SQL design view is now:

SELECT PSFSP8_PS_LEDGER.BUSINESS_UNIT, PSFSP8_PS_LEDGER.LEDGER, PSFSP8_PS_LEDGER.ACCOUNT, PSFSP8_PS_LEDGER.DEPTID, PSFSP8_PS_LEDGER.PRODUCT, PSFSP8_PS_LEDGER.FISCAL_YEAR, PSFSP8_PS_LEDGER.ACCOUNTING_PERIOD, PSFSP8_PS_LEDGER.POSTED_TOTAL_AMT
FROM PSFSP8_PS_LEDGER
WHERE (((PSFSP8_PS_LEDGER.LEDGER)="ACTUALS"));

Open in new window

So I just want to be sure I am understanding, you are saying my best bet is to figure out the IIF statements ?

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

I did have this portion below originally when I was trying to figure out how to run it I copied this from another DB and changed it to meet what I'm trying to do. When would I use this method - when I do or do not have a passthrough...when I do not, correct?

On Error Resume Next
DoCmd.DeleteObject acQuery, "PS_Ledger"
On Error GoTo 0

Set myQueryDef = dbs.CreateQueryDef("PS_Ledger")
myQueryDef.Connect = "ODBC;DSN=PSFSP8;UID=XXXX;PWD=XXXXX;MODE=SHARE;DBALIAS=PSFSP8;IGNOREWARNINGS=1;DISABLEUNICODE=1;DISABLEKEYSETCURSOR=1;"
myQueryDef.ODBCTimeout = 6000
myQueryDef.SQL = strsql
myQueryDef.Close

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

I think I would change the Set myQueryDef = dbs.CreateQueryDef("PS_Ledger") part to Set myQueryDef = dbs.CreateQueryDef("PSFSP8_PS_Ledger") if that is the actual passthrough I already created IF I'm supposed to do the dbs.createquerydef piece when I ALREADY have a passthrough created. I'm admittingly lost on that part. It seems like that part that I copied is deleting a query if one exists and creating a new one....so are these additional lines actually creating a passthrough in VBA and one in DB is not needed?

LVL 12

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented: 2017-11-10

This is quickly getting complicated :=), as I don't know how familiar you are with the different terms.

When access executes a LOCAL query, even if it is against linked external tables, the access database engine (ACE if 2007 or newer) will try to evaluate the best way to execute that query. I would say that overall Access (or ACE) is actually pretty good at this. However sometimes, if the query is complex (or poorly written) ACE is forced to download the entire table and do joins, or functional expressions local. This can be painfully slow. Sometimes ACE will be smart enough to send parts of the query directly to the server, if it believes its a good idea. Now when using ODBC this also means that the SQL you typed is being read, and interpreted by ACE, then translated by the ODBC driver into a syntax that the Server can understand. Sometimes this can cause issues, but usually its quite painless and transparant for users.

Now the other option is to specify a passthrough. In this case, the sql string is passed directly, and unaltered to the server for processing there.
This also means the passthrough needs to use the SQL syntax of the server, completely ignoring the syntax of Access.

The downside of a passthrough is that they are not updateable. So if that is a requirement, then yes, perhaps an if statement is the way to go.
If you are mainly doing reporting, then a passthrough can be an excellent choice.
The code shown is post is one way of defining a passthrough in code.

So if you need variables for that passthrough, you alter the sql.
Lets take a simple example, we want to select from the customers table, everyone with firstname Joe.

Dim SQL as string
'Note that I use single quotes, like SQL server, unlike access where I might use double quotes
Dim SQL as string
SQL="Select * from dbo.tbl_customers where firstname='joe'"

     dim myQueryDef as dao.QueryDef
    Set myQueryDef = dbs.CreateQueryDef("") 'Pass in a empty string to create a query and not save it. Saves the hazle of deleting it again.
    myQueryDef.Connect = "ODBC;DSN=PSFSP8;UID=XXXX;PWD=XXXXX;MODE=SHARE;DBALIAS=PSFSP8;IGNOREWARNINGS=1;DISABLEUNICODE=1;DISABLEKEYSETCURSOR=1;"
    myQueryDef.ODBCTimeout = 6000
    myQueryDef.SQL = SQL
  dim myRecordset as dao.Recordset
  Set myRecordset=myQueryDef.OpenRecordset(dbOpenSnapshot,dbSQLPassThrough)

Open in new window

Now you can build the SQL string any way you like, to include your parameters.

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

Ah, ok! I was starting to think, "why the heck did I go down this rabbit hole again??" when I started re-creating my regular passthrough. But now that you have reinforced - it's because of the variables. They will change each month (just accounting period, and then next year - fiscal year). I'm going to digest this and work on it this weekend. Thanks - I will update when I've made progress!! I appreciate all of your help guys, have a great weekend and talk next week.

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-10

I forgot to ask this question - for the wildcard % for Product and Dept, should I use equal sign or "LIKE" in my strSql ?

LVL 12

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented: 2017-11-10

For wildcards you should use like. Equal can only be used when they are equal.

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-12

I've made progress and now have some my variables working. The user will just have limited use for this db and pulling just these two accounts. I My variables for Fiscal Year and Accounting Period are passing successfully, however, I'm stuck on the second set of CASE statements that will create a final column that sums different accounting balances.

I believe this is a new question, so I will post that separately and mark this one complete. Thank you for all of your help, here is what finally worked (minus case statements):

Private Sub Command43_Click()
Dim dbs As DAO.Database
Dim myQueryDef As DAO.QueryDef
Dim strsql As String

Set dbs = CurrentDb()

Private Sub Command43_Click()
    Dim dbs As DAO.Database
    Dim myQueryDef As DAO.QueryDef
    Dim strsql As String
  
      
    Set dbs = CurrentDb()
      
    strsql = "SELECT A.ACCOUNT, B.DESCR, A.DEPTID, A.PRODUCT, A.FISCAL_YEAR,SUM(  DECIMAL( CASE WHEN ACCOUNTING_PERIOD = " & Me.AcctPer & " THEN POSTED_TOTAL_AMT ELSE 0 END,19, 2)), " & _
            "FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
            "WHERE (A.BUSINESS_UNIT = 'WDBIL' AND A.LEDGER = 'ACTUALS' AND A.ACCOUNT IN ('116050','116200') AND B.SETID = 'WDUSA' AND A.ACCOUNT = B.ACCOUNT) AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ACCOUNT = B_ED.ACCOUNT AND B_ED.EFFDT <= CURRENT DATE) " & _
            "AND A.FISCAL_YEAR = " & Me.FiscYear & " " & _
            "AND A.ACCOUNTING_PERIOD = " & Me.AcctPer & " " & _
            "OR A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907) " & _
            "GROUP BY A.ACCOUNT, B.DESCR, A.DEPTID, A.PRODUCT, A.FISCAL_YEAR " & _
            "ORDER BY DEPTID, PRODUCT WITH UR;"

Open in new window

Erika AndersonAccounting SystemsAuthor Commented: 2017-11-12

Much appreciated!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

SQL

From novice to tech pro — start learning today.

SQL

By:

Ken Mayer

70-642 R5 - Configuring Windows Server 2008 R2 Networ…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

356 lessons
SQL

By:

Ken Mayer

70-643 - Windows Server 2008 Applications Infrastructure

Premium

Premium members can enroll in this course at no extra cost.
Learn More

342 lessons
Programming

By:

Peter Thorsteinson

C++ 11: Fundamentals

Premium

Premium members can enroll in this course at no extra cost.
Learn More

29 lessons
Programming

By:

Peter Thorsteinson

C++ 11: Intermediate

Premium

Premium members can enroll in this course at no extra cost.
Learn More

53 lessons
Microsoft Applications

By:

Sandra Batakis

Certification
Certification: MOS Microsoft Office Specialist - Access 2007 Ad…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

179 lessons
Microsoft Applications

By:

Sandra Batakis

Certification
Certification: MOS Microsoft Office Specialist - Access 2007 Ba…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

228 lessons
Microsoft Applications

By:

Sandra Batakis

Certification
Certification: MOS Microsoft Office Specialist - Access 2007 Int…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

207 lessons
Microsoft Office

By:

Drew Fierst

Certification
Certification: MOS Microsoft Office Specialist - Access 2010 Ad…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

171 lessons
Microsoft Office

By:

Drew Fierst

Certification
Certification: MOS Microsoft Office Specialist - Access 2010 Ba…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

233 lessons
Microsoft Office

By:

Drew Fierst

Certification
Certification: MOS Microsoft Office Specialist - Access 2010 Int…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

214 lessons