Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem SQL query with CASE statement in MS Access

Posted on 2010-11-19
13
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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