By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

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

P: 4
The following section, from an sql query, runs perfectly in Microsoft SQL Server Management Studio:
Expand|Select|Wrap|Line Numbers
  1. the_section=(case when line_no= 101 then 1 when line_no  between 102 
  2. and 105 then 2 when line_no  between 106 
  3. and 112 then 2 when line_no  between 201 
  4. and 219 then 3 when line_no  between 703 
  5. and 704 then 2 when line_no  between 801 
  6. and 822 then 2 when line_no  between 901 
  7. and 910 then 2 when line_no  between 1001 
  8. and 1016 then 2 when line_no  between 1101 
  9. and 1128 then 2 when line_no  between 1201 
  10. and 1210 then 2 when line_no  between 1301 
  11. 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_section" 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=(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'. Make sure the object exists and that you spell its name and the path name correctly.

Can someone please help?
Apr 22 '10 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 22 '10 #2

P: 4
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
Apr 22 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
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!
Apr 22 '10 #4

P: 4
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
Apr 22 '10 #5

P: 6
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
Apr 23 '10 #6

P: 6
@Lawless
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.
Apr 23 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 23 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.