Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem SQL query with CASE statement in MS Access

Posted on 2010-11-19
13
Medium Priority
?
401 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:spiral2007
10 Comments
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172199
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
 

Author Comment

by:spiral2007
ID: 34172215
And how can i do that?
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172235
in access 2007-2010 it's on the design ribbon
older versions, right click the query window and select sql specific...passthrough
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:spiral2007
ID: 34172265
If I turn it to pass through I can't find it in my Integration Services Solution.
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172336
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
 
LVL 9

Accepted Solution

by:
Stephan_Schrandt earned 1000 total points
ID: 34172350
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
 

Author Comment

by:spiral2007
ID: 34172356
It's access data so it's the second way.
Thanks.
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 34172401
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
 
LVL 12

Assisted Solution

by:ErezMor
ErezMor earned 1000 total points
ID: 34172418
oops, missed the substring function, which is a t-sql too, change it to Left(T_DROMOS.TXCODE,2)
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 34387646
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question