Pass-throuth query programing

[Fernando Costa]

I get this error:

Run·time error '3075':
Erro de sintaxe (operador em falta) na expressão de consulta 'CASE
WHEN FIELD1 = 'VALUE' THEN 1 ELSE 0 END',

When creating a sql pass-throuth query with vba, when using:
"CASE WHEN FIELD1 = 'VALUE' THEN 1 ELSE 0 END" in the middle of the string destined to write to the query.
If that string is pasted inside the query, it works.

Any help will be welcome


thank you

[CJ_London]

not enough information to provide an answer - you need to provide the whole code used to create the sql

[Fernando Costa]

not enough information to provide an answer - you need to provide the whole code used to create the sql

I send the complete code with the characteristics of the database adjusted.
But I'll say again that by removing the line relating to "CASE WHEN ...", the query works.



Sub makeQuery1()
Dim sql As String
sql = "SELECT " & _
" T2.FCY_0 AS unidade, " & _
" T1.EMP_0 AS numero, " & _
" T2.YNOME_COMP AS nome, " & _
" T2.NUMSSC_0 AS niss, " & _
" YEAR(T1.DAT_0) AS ano, " & _
" MONTH(T1.DAT_0) AS mes, " & _
" 'VENCIMENTO' AS tipo, " & _
" AVG(T1.VARVAL_0) AS vencimento, " & _
" CASE WHEN (Year([DAT_0]) * 12 + Month([DAT_0])) <> <anomes> THEN 'ANTERIOR' ELSE 'ACTUAL' END AS Expr1 " & _
" FROM T1 INNER JOIN T2 ON T1.EMP_0 = T2.REFNUM_0 " & _
" WHERE (T1.VARCOD_0 = 'VENCIMENTO') AND (YEAR(T1.DAT_0) * 12 + MONTH(T1.DAT_0) BETWEEN <anomes> - 1 AND <anomes>) " & _
" GROUP BY T2.FCY_0, T1.EMP_0, T2.YNOME_COMP_0, T2.NUMSSC_0, YEAR(T1.DAT_0), MONTH(T1.DAT_0) " & _
" ORDER BY numero"
sql = Replace(sql, "<anomes>", 2019 * 12 + 1)
createPassThrouthSQLquery "FCTven", sql
End Sub



Sub createPassThrouthSQLquery(ByVal queryName As String, sql As String)
Dim qd As QueryDef
Dim cn As String
cn = "ODBC;DRIVER=SQL Server;SERVER=SERVER1;UID=USER;PWD=PASS;DATABASE=D B"

On Error Resume Next
DoCmd.Close acQuery, queryName
DoCmd.DeleteObject acQuery, queryName
On Error Resume Next

Set qd = CurrentDb.CreateQueryDef(queryName)
qd.sql = sql
qd.Connect = cn
qd.ReturnsRecords = True
qd.Close

End Sub

[pbaldy]

You can use this to see/test the finished SQL (after the replace function):

http://www.baldyweb.com/ImmediateWindow.htm

If you don't see the problem, post that here. What version of SQL Server? The Year and Month functions weren't added until v2008 I think.

[CJ_London]

I thought you might have missed a space or comma or exceeded the number of line continuations allowed, but that does not appear to be the case. The only thing I can see is that DAT_0 is referred to elsewhere as T1.DAT_0, is there a DAT_0 in T2?

you are presumably getting the error when you open the query. So it might be worth swapping these two lines around
qd.sql = sql
qd.Connect = cn

[Fernando Costa]

I thought you might have missed a space or comma or exceeded the number of line continuations allowed, but that does not appear to be the case. The only thing I can see is that DAT_0 is referred to elsewhere as T1.DAT_0, is there a DAT_0 in T2?

you are presumably getting the error when you open the query. So it might be worth swapping these two lines around
qd.sql = sql
qd.Connect = cn

This problem of DAT_0 has to be with the change I made so that the data related to the database, server, user and password are not real ones.
Your suggestion to get the final string, I already did, as I mentioned the first time and taking this string and pasting inside the query works perfect!
The error occurs when executing the line: "qd.sql = sql"
It seems that the vba does some conference at the time of loading the sql of a query and does not allow "CASE" in the string !!! ridiculous


The sql version is: Microsoft SQL Server 2008 R2 (SP3)
But this does not matter because sql executes the code.
The access vba is that it does not allow putting the string in the "query.sql"




I just solved the problem ...
uffa !!!
I passed the line: "qd.sql = sql" to the end, before closing the querydef and this way it works


Set qd = CurrentDb.CreateQueryDef(queryName)
qd.Connect = cn
qd.ReturnsRecords = True
qd.sql = sql
qd.Close

Thank you for the time you spent

[pbaldy]

Glad you got it sorted and welcome to the site by the way!

[CJ_London]

which is what I suggested