Problem SQL query with CASE statement in MS Access

Hello experts.

I wrote a query to create a view in MS Access. When I tried to re-write it in t-sql in MS Access, in order to use it with Intergration Services, I can't save it because there is an error.
The error message I get is: "Syntax error (missing operator) in query expression 'CASE WHEN ...'."

Thanks.

SELECT DISTINCT SUBSTRING(T_DROMOS.TXCODE,1,2) AS ZONI_ID, T_DROMOS.TXCODE, T_DROMOS.TXDESC, T_DROMOS.TXJAC1, T_DROMOS.TXCHAM, 
CASE 
WHEN CONTROL.COADR1 Like '%VLACHOS%' OR CONTROL.COADR2 Like '%VLACHOS%' OR CONTROL.COADR3 Like '%VLACHOS%'
THEN 1
WHEN CONTROL.COADR1 Like '%FANOURIOU%' OR CONTROL.COADR2 Like '%FANOURIOU%' OR CONTROL.COADR3 Like '%FANOURIOU%'
THEN 2
WHEN CONTROL.COADR1 Like '%LOUCA%' OR CONTROL.COADR2 Like '%LOUCA%' OR CONTROL.COADR3 Like '%LOUCA%' 
THEN 3
ELSE -1
END AS ENORIAID
FROM T_DROMOS INNER JOIN TXCONN ON T_DROMOS.TXCODE=SUBSTRING(TXCONN.TXTAXC,1,5) INNER JOIN CONTROL ON TXCONN.TXTAXP=CONTROL.COCODE;

Open in new window

spiral2007Asked:
Who is Participating?
 
Stephan_SchrandtConnect With a Mentor Commented:
msaccess does not provide the inbuilt functions like t-sql. But access can use vba functions do simulate this. Add a vba module to your database and paste the code snippet. Then in the query use the function like this:

SELECT DISTINCT SUBSTRING(T_DROMOS.TXCODE,1,2) AS ZONI_ID, T_DROMOS.TXCODE, T_DROMOS.TXDESC, T_DROMOS.TXJAC1, T_DROMOS.TXCHAM,
calfield(CONTROL.COADR1,CONTROL.COADR2,CONTROL.COADR3) AS ENORIAID
FROM T_DROMOS.....


Option Explicit

Public Function calfield(value1, value2, value3) As String
    If InStr(1, value1, "VLACHOS", vbTextCompare) > -1 Or InStr(1, value2, "VLACHOS", vbTextCompare) > -1 Or InStr(1, value3, "VLACHOS", vbTextCompare) > -1 Then
        calfield = 1
    ElseIf InStr(1, value1, "FANOURIOU", vbTextCompare) > -1 Or InStr(1, value2, "FANOURIOU", vbTextCompare) > -1 Or InStr(1, value3, "FANOURIOU", vbTextCompare) > -1 Then
        calfield = 2
    ElseIf InStr(1, value1, "LOUCA", vbTextCompare) > -1 Or InStr(1, value2, "LOUCA", vbTextCompare) > -1 Or InStr(1, value3, "LOUCA", vbTextCompare) > -1 Then
        calfield = 3
    Else
        calfield = 4
    End If
End Function

Open in new window

0
 
ErezMorCommented:
access uses a different dialect of sql than sql erver (t-sql)
if your query is intended to run on sql server's data from access, you need to turn your query type to a "pass-through" query, which does as it's name implies, passes the whole syntax "as is" through to sql server without interpetting (hence no syntax errors...)
0
 
spiral2007Author Commented:
And how can i do that?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ErezMorCommented:
in access 2007-2010 it's on the design ribbon
older versions, right click the query window and select sql specific...passthrough
0
 
spiral2007Author Commented:
If I turn it to pass through I can't find it in my Integration Services Solution.
0
 
ErezMorCommented:
regardless of integration services,
if it's sql server's data, then create it directly in sql server as a view or storeprocedure, if it's access data, then change the t-sql sentences to jet-sql so it can be saved as normal access query
tell me which way is going to be and we'll proceed from there
0
 
spiral2007Author Commented:
It's access data so it's the second way.
Thanks.
0
 
ErezMorCommented:
a vba function is  one way, but you can still do with only sql if you change case statements to IIF (immediate if), so your t-sql should be something like:


SELECT DISTINCT SUBSTRING(T_DROMOS.TXCODE,1,2) AS ZONI_ID, T_DROMOS.TXCODE, T_DROMOS.TXDESC, T_DROMOS.TXJAC1, T_DROMOS.TXCHAM,
IIF(CONTROL.COADR1 Like '*VLACHOS*' OR CONTROL.COADR2 Like '*VLACHOS*' OR CONTROL.COADR3 Like '*VLACHOS*',1,
IIF(CONTROL.COADR1 Like '*FANOURIOU*' OR CONTROL.COADR2 Like '*FANOURIOU*' OR CONTROL.COADR3 Like '*FANOURIOU*',2,
IIF(CONTROL.COADR1 Like '*LOUCA*' OR CONTROL.COADR2 Like '*LOUCA*' OR CONTROL.COADR3 Like '*LOUCA*',3,-1))) AS ENORIAID
FROM T_DROMOS INNER JOIN TXCONN ON T_DROMOS.TXCODE=SUBSTRING(TXCONN.TXTAXC,1,5) INNER JOIN CONTROL ON TXCONN.TXTAXP=CONTROL.COCODE;
0
 
ErezMorConnect With a Mentor Commented:
oops, missed the substring function, which is a t-sql too, change it to Left(T_DROMOS.TXCODE,2)
0
 
QlemoDeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
All Courses

From novice to tech pro — start learning today.