SQL query runs in Microsoft SQL Server Management Studio but not in Access 2007

[Lawless]

The following section, from an sql query, runs perfectly in Microsoft SQL Server Management Studio:

the_section=(case when line_no= 101 then 1 when line_no  between 102 
and 105 then 2 when line_no  between 106 
and 112 then 2 when line_no  between 201 
and 219 then 3 when line_no  between 703 
and 704 then 2 when line_no  between 801 
and 822 then 2 when line_no  between 901 
and 910 then 2 when line_no  between 1001 
and 1016 then 2 when line_no  between 1101 
and 1128 then 2 when line_no  between 1201 
and 1210 then 2 when line_no  between 1301 
and 1310 then 2 else 3 end),

The table column "line_no" exists in one of the linked tables that is part of the query but the column "the_sectio n" is being created to hold data only for the query.

When I try to run this in Access 2007 I get the following error message:

The Microsoft Office Access database engine could not find 'the_section=(c ase when line_no=101 then 1 when line_no between 102
and 105 then 2 when line_no between 106
and 112 then 2 when line_no between 201
and 219 then 3 when line_no between 703
and 704 then 2 when line_no between 801
and 822 then'. Make sure the object exists and that you spell its name and the path name correctly.

Can someone please help?

[NeoPa]

SQL Server uses a flavour of SQL called T-SQL or Transact-SQL. The flavour used in Access is Jet SQL. They are not fully compatible. You cannot simply copy/paste from one to the other.

[Lawless]

Thank You -- That solves the problem -- I see in Jet SQL that I have to use an "IIF" as opposed to "case when" in T-SQL

[NeoPa]

You also have other functions available for choosing results dependent on other data, such as Choose() & Switch(). The most common one seems to be IIf() though, certainly.

Welcome to Bytes!

[Lawless]

With T-SQL if you write something like: amount = 1 and amount is not a defined column in the table you are using to draw data from, it will make a column named "amount" and fill it with the value 1

In Jet-SQL with Microsoft Acces if you do this you get a pop up box that ask you to enter the parameter value for "amount".

Why and how can this be changed to get the same results that I get with T-SQL

[hilaDG]

Why not simply link the table from SQL to access?

from my experience, SQL handles all those CASE.. WHEN way more gracefully then MS-access.
Create an ODBC server, and go to the tables and use link tables in order to link to that SQL server. This way you don't have to re-create the entire table definitions, etc, with MS-Access

[hilaDG]

With T-SQL if you write something like: amount = 1 and amount is not a defined column in the table you are using to draw data from, it will make a column named "amount" and fill it with the value 1

In Jet-SQL with Microsoft Acces if you do this you get a pop up box that ask you to enter the parameter value for "amount".

Why and how can this be changed to get the same results that I get with T-SQL

If you don't have a column defined access would never define one for you. It could be so many things other then a column: a form, a text box, a query, that access simply cannot assume a column. So if you want one you'll have to define it by yourself.

[NeoPa]

It seems you're confusing columns with variables (If my T-SQL knowledge is at all reliable). I must admit I have never used a variable name that didn't start with an @. I didn't realise it was even supported. I'm trying to think if maybe my understanding is just too limited and perhaps it is a column after all, but I cannot imagine how that would make any sense at all. I certainly know of know way to duplicate either in Jet SQL.

I would add one other comment that you can take on board if you like, that I never recommend the use of any type of variable, even a temporary one, that is not already defined or declared. On-the-fly variables are the cause of so many problems, especially in the area of maintenance.