Pass Through Query: CASE statement

[eww]

I'm having issues with creating a pass through query through the VBA code. I've run the whole query statement through a regular pass through query (creating a query in design SQL mode, not through VBA) and it runs just fine. However when i try to run the code it fails and gives me this error:

"Syntax error (missing operator) in query expression 'OverUnder = CASE JobInfo.BillType WHEN 'Unit Price' THEN 0 ELSE (JobInfo.AdjWgt - (Submittals.DetWgt + JobInfo.BalToDet)) END'."

Again, I've run this through a regular query and it runs just fine:

        strSQL = "SELECT Loc.Area, JobInfo.JobNum, JobInfo.JobName, JobInfo.Cstmr1, " & _
        "Employees.FirstName + ' ' + Employees.LastName AS Detlr, JobInfo.Sales, MrktCat.MrktCatDesc AS MrktCat, " & _
        "JobInfo.BillType, JobInfo.LastInvoice, JobInfo.EstWgt, JobInfo.AdjWgt, JobInfo.DelWgt, JobInfo.BalToDet, " & _
        "JobInfo.EstLastDel, JobInfo.Comments,JobInfo.DetStatus, JobInfo.JobStatus, Submittals.DetWgt, " & _
        "(JobInfo.DelWgt/(Submittals.DetWgt+JobInfo.BalToDet)) AS PercDel, " & _
        "(Submittals.DetWgt/(Submittals.DetWgt+JobInfo.BalToDet)) AS PercDet, " & _
        "OverUnder = CASE JobInfo.BillType WHEN 'Unit Price' THEN 0 ELSE (JobInfo.AdjWgt - (Submittals.DetWgt + JobInfo.BalToDet)) END, " & _
        "(JobInfo.AdjWgt-JobInfo.DelWgt) AS BalToDel " & _
        "FROM (SELECT JobNum, SUM(DetWgt) AS DetWgt FROM ntblSubmittals GROUP BY JobNum) AS Submittals " & _
        "JOIN ntblJobInfo AS JobInfo ON Submittals.JobNum = JobInfo.JobNum " & _
        "JOIN ntblJobUsers AS JobUsers ON JobUsers.JobNum = JobInfo.JobNum " & _
        "JOIN ntblEmployees AS Employees ON Employees.Username = JobUsers.Username " & _
        "JOIN ntblMrktCat AS MrktCat ON MrktCat.MrktCatCode = JobInfo.MrktCat " & _
        "JOIN ntblLoc AS Loc ON (Loc.FabLoc = JobInfo.SoldLoc OR Loc.FabLoc = JobInfo.DetLoc OR Loc.FabLoc = JobInfo.FabLoc) " & _
        "WHERE ((JobInfo.JobStatus = 'Open' Or JobInfo.JobStatus = 'On Hold') And JobUsers.Role = 'Lead Detailer')" & _
        "ORDER BY JobInfo.JobNum"
        Set qryDef = dbCMC.CreateQueryDef(strQueryName, strSQL)
        qryDef.Connect = "ODBC;DSN=******;Description=******;UID=******;PWD=****;"

[NoellaG]

Hi,

Could it be your coded query is seen as a regular Access query, not a pass-through query, so Access tries to parse it instead of leaving it to SQL server?
Have you tried altering the working SQL statement of an existing pass-through query with your code and see if it still works?

[eww]

I suppose that is possible, although i figured the qryDef.Connect would take care of that (not sure if there's a a way to actually declare that it's specifically a pass through query).

Another thing to note, I took out the OverUnder CASE statement (shouldn't affect anything, just selecting it, not using it anywhere else in the query) and it's now telling me I've got an error in my FROM clause, which looks fine to me.

[NoellaG]

Hi,

so, when it doesn't see the CASE statement anymore (I don't think CASE works in Access SQL, it's SQL Server language) it moves to the next mistake (Access wants to see 'INNER' or 'OUTER' before the JOIN expression). Gives me really the idea Access is parsing your query before sending it to the server.

gr
NG

[eww]

Noella, went with your suggestion. Instead of recreating queries everytime (duh this seems smarter anyway...) I'm going to just leave a generic and change the SQL of it. This seems to work, here's the code that I've changed to (SQL from above has not changed)

        Set qryDef = dbCMC.QueryDefs("qryBacklog")
        qryDef.SQL = strSQL

Thanks for the help

[NoellaG]

Hi,

maybe 1 suggestion: using an ADODB.command object instead of a DAO.querydef...

anyway succes
NG

[eww]

In regards to your 2nd reply, Yes and no. CASE is SQL, not Access SQL, but the point of the pass-through is that SQL is doing the prossessing so you have to have CASE, instead of say IIF. Same goes for the JOINs. However you were right I think in regards to just changing the SQL of an existing pass-through, and that would be why it wasn't working, it was creating a simple SELECT query and not a pass through (therefor CASE, JOIN, weren't working).

You might be right about ADO, but i'm fine with changing existing pass-throughs. thanks again.