Case When statement in Access Frontend with Sql Backend

I am trying update my queries to perform better on my recently transferred
backend from Access to Sql Server. So I need to change a switch statement in
Access to a Case When statement. I have made the adjustement as shown below
and I can't save it because Access says missing operator. I don't know if my
syntax is wrong or if access doesn't like the case when statement. Thanks for
the thoughts.

'AssetClassSort' = CASE
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
END

I also had END AS AssetClassSort

If you are using ODBC tables for your access to the SQL-Server backend, then
you must not change anything to your queries and you must keep the regular
Access/JET syntaxe. If this is a passthrough query or an ADP query, then
it's wrong on many ways. As you didn't provide any detail on what you are
doing exactly, it's impossible for me to tell you anything more on that
because I won't start covering every possible combinations.

Finally, you should take a look at the difference between multiposting and
crossposting:

http://www.blakjak.demon.co.uk/mul_crss.htm
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"icccapital" wrote in message
...
I am trying update my queries to perform better on my recently transferred
backend from Access to Sql Server. So I need to change a switch statement
in
Access to a Case When statement. I have made the adjustement as shown
below
and I can't save it because Access says missing operator. I don't know if
my
syntax is wrong or if access doesn't like the case when statement. Thanks
for
the thoughts.

'AssetClassSort' = CASE
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
  WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
 END

I also had END AS AssetClassSort

Access SQL doesn't support Case When. You can use the syntax on SQL Server or
in a pass-through query.

I'm not sure if the syntax is copied directly but the quotes around
'AssetClassSort' suggests you are attempting to assign a number to a
string/text value.
--
Duane Hookom
Microsoft Access MVP


"icccapital" wrote:

I am trying update my queries to perform better on my recently transferred
backend from Access to Sql Server. So I need to change a switch statement in
Access to a Case When statement. I have made the adjustement as shown below
and I can't save it because Access says missing operator. I don't know if my
syntax is wrong or if access doesn't like the case when statement. Thanks for
the thoughts.

'AssetClassSort' = CASE
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
 END

I also had END AS AssetClassSort

Well, as I stated in my post I am looking to transfer my backend database
(linked to access in the frontend) from access to SQL Server Express. After
upsizing the database to a test database and trying to run some of the
queries they were very slow, and so after some research I found that
"optimizing" the queries was necessary (ie make them more transact sql
queries) so that Access doesn't have to parse the query and make its own
queries that is can then send on to SQL Server, which after setting up
traceSQLMode and looking in sqlout.txt it turned out that my queries were
being turned into 5 or 6. So that is the reason.

I have read on passthrough queries, but articles are vague on issues with
passthrough queries as recordsource for a report.

I hope that helps to clarify my issue. Please clarify why "If this is a
passthrough query or an ADP query, then it's wrong on many ways." And ask
any leading questions that will help to clarify my issues. thanks.

"Sylvain Lafontaine" wrote:

If you are using ODBC tables for your access to the SQL-Server backend, then
you must not change anything to your queries and you must keep the regular
Access/JET syntaxe. If this is a passthrough query or an ADP query, then
it's wrong on many ways. As you didn't provide any detail on what you are
doing exactly, it's impossible for me to tell you anything more on that
because I won't start covering every possible combinations.

Finally, you should take a look at the difference between multiposting and
crossposting:

http://www.blakjak.demon.co.uk/mul_crss.htm
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"icccapital" wrote in message
...
I am trying update my queries to perform better on my recently transferred
backend from Access to Sql Server. So I need to change a switch statement
in
Access to a Case When statement. I have made the adjustement as shown
below
and I can't save it because Access says missing operator. I don't know if
my
syntax is wrong or if access doesn't like the case when statement. Thanks
for
the thoughts.

'AssetClassSort' = CASE
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
  END

I also had END AS AssetClassSort


.

Thanks Duane,

That was a misunderstanding of syntax on my part for the case when. I had
originally

CASE......END AS AssetClassSort

but changed it when things weren't working and I forgot to change it back.
AssetClassSort is just supposed to be the field name.

"Duane Hookom" wrote in message
...
Access SQL doesn't support Case When. You can use the syntax on SQL Server
or
in a pass-through query.

I'm not sure if the syntax is copied directly but the quotes around
'AssetClassSort' suggests you are attempting to assign a number to a
string/text value.
--
Duane Hookom
Microsoft Access MVP


"icccapital" wrote:

I am trying update my queries to perform better on my recently
transferred
backend from Access to Sql Server. So I need to change a switch
statement in
Access to a Case When statement. I have made the adjustement as shown
below
and I can't save it because Access says missing operator. I don't know
if my
syntax is wrong or if access doesn't like the case when statement. Thanks
for
the thoughts.

'AssetClassSort' = CASE
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
 END

I also had END AS AssetClassSort

There are many ways of upsizing an Access database to SQL-Server and
querying it after that. Simply saying that you are querying a SQL-Server
database that has been upsized tell us nothing on how you are accessing it
right now and saying that you want to optimize them is not any more
informative either.

From your last post, it now appears that you are using ODBC Linked Tables.
When using directly ODBC linked tables, you cannot do anything to "optimize"
the queries created in Access. They are as they are, using the regular
syntax of Access/JET and that's all. The only thing you can do is to
create Views on SQL-Server and link to these views as if they are tables.
This might bring some relief on the performance issue but don't expect
anything sky-rocketing and even in these cases, the queries that you are
building in Access remains the same; with the exception that you will
replace some JOIN between two or more tables with a View. If you want to
have your Views updatable, see http://support.microsoft.com/kb/q209123/

With passthrough queries, your queries are executed directly on SQL-Server;
so in this case, yes, you must now use the regular syntax of SQL-Server;
also known as T-SQL. However, passhtrough queries are read-only and while
you can use them as the record source for a report, you cannot use them for
sub-reports.

The other options is to use unbound forms or ADP. With unbound forms, you
manage yourself the whole process of querying/updating data by making call
with DAO or ADO and filling up your forms with VBA code. With ADP, it's
also a whole new story; completely different from the other options. See
the ADP newsgroup for more details.

If you want to follow what happens between Access and SQL-Server, using the
SQL-Server Profiler is a better way then with the traceSQLMode of ODBC. With
the later, there are many things in the tracout that I never been able to
figure out.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"icccapital" wrote in message
...
Well, as I stated in my post I am looking to transfer my backend database
(linked to access in the frontend) from access to SQL Server Express.
After
upsizing the database to a test database and trying to run some of the
queries they were very slow, and so after some research I found that
"optimizing" the queries was necessary (ie make them more transact sql
queries) so that Access doesn't have to parse the query and make its own
queries that is can then send on to SQL Server, which after setting up
traceSQLMode and looking in sqlout.txt it turned out that my queries were
being turned into 5 or 6. So that is the reason.

I have read on passthrough queries, but articles are vague on issues with
passthrough queries as recordsource for a report.

I hope that helps to clarify my issue. Please clarify why "If this is a
passthrough query or an ADP query, then it's wrong on many ways." And ask
any leading questions that will help to clarify my issues. thanks.

"Sylvain Lafontaine" wrote:

If you are using ODBC tables for your access to the SQL-Server backend,
then
you must not change anything to your queries and you must keep the
regular
Access/JET syntaxe. If this is a passthrough query or an ADP query, then
it's wrong on many ways. As you didn't provide any detail on what you
are
doing exactly, it's impossible for me to tell you anything more on that
because I won't start covering every possible combinations.

Finally, you should take a look at the difference between multiposting
and
crossposting:

http://www.blakjak.demon.co.uk/mul_crss.htm
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"icccapital" wrote in message
...
I am trying update my queries to perform better on my recently
transferred
backend from Access to Sql Server. So I need to change a switch
statement
in
Access to a Case When statement. I have made the adjustement as shown
below
and I can't save it because Access says missing operator. I don't know
if
my
syntax is wrong or if access doesn't like the case when statement.
Thanks
for
the thoughts.

'AssetClassSort' = CASE
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
  END

I also had END AS AssetClassSort


.

Thank you very much Sylvain for those thoughts. There is a lot there to use
and consider as I work through the "remodel" of the sytem.


"Sylvain Lafontaine" wrote in message
...
There are many ways of upsizing an Access database to SQL-Server and
querying it after that. Simply saying that you are querying a SQL-Server
database that has been upsized tell us nothing on how you are accessing it
right now and saying that you want to optimize them is not any more
informative either.

From your last post, it now appears that you are using ODBC Linked Tables.
When using directly ODBC linked tables, you cannot do anything to
"optimize" the queries created in Access. They are as they are, using the
regular syntax of Access/JET and that's all. The only thing you can do
is to create Views on SQL-Server and link to these views as if they are
tables. This might bring some relief on the performance issue but don't
expect anything sky-rocketing and even in these cases, the queries that
you are building in Access remains the same; with the exception that you
will replace some JOIN between two or more tables with a View. If you
want to have your Views updatable, see
http://support.microsoft.com/kb/q209123/

With passthrough queries, your queries are executed directly on
SQL-Server; so in this case, yes, you must now use the regular syntax of
SQL-Server; also known as T-SQL. However, passhtrough queries are
read-only and while you can use them as the record source for a report,
you cannot use them for sub-reports.

The other options is to use unbound forms or ADP. With unbound forms, you
manage yourself the whole process of querying/updating data by making call
with DAO or ADO and filling up your forms with VBA code. With ADP, it's
also a whole new story; completely different from the other options. See
the ADP newsgroup for more details.

If you want to follow what happens between Access and SQL-Server, using
the SQL-Server Profiler is a better way then with the traceSQLMode of
ODBC. With the later, there are many things in the tracout that I never
been able to figure out.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"icccapital" wrote in message
...
Well, as I stated in my post I am looking to transfer my backend database
(linked to access in the frontend) from access to SQL Server Express.
After
upsizing the database to a test database and trying to run some of the
queries they were very slow, and so after some research I found that
"optimizing" the queries was necessary (ie make them more transact sql
queries) so that Access doesn't have to parse the query and make its own
queries that is can then send on to SQL Server, which after setting up
traceSQLMode and looking in sqlout.txt it turned out that my queries were
being turned into 5 or 6. So that is the reason.

I have read on passthrough queries, but articles are vague on issues with
passthrough queries as recordsource for a report.

I hope that helps to clarify my issue. Please clarify why "If this is a
passthrough query or an ADP query, then it's wrong on many ways." And
ask
any leading questions that will help to clarify my issues. thanks.

"Sylvain Lafontaine" wrote:

If you are using ODBC tables for your access to the SQL-Server backend,
then
you must not change anything to your queries and you must keep the
regular
Access/JET syntaxe. If this is a passthrough query or an ADP query,
then
it's wrong on many ways. As you didn't provide any detail on what you
are
doing exactly, it's impossible for me to tell you anything more on that
because I won't start covering every possible combinations.

Finally, you should take a look at the difference between multiposting
and
crossposting:

http://www.blakjak.demon.co.uk/mul_crss.htm
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"icccapital" wrote in message
...
I am trying update my queries to perform better on my recently
transferred
backend from Access to Sql Server. So I need to change a switch
statement
in
Access to a Case When statement. I have made the adjustement as shown
below
and I can't save it because Access says missing operator. I don't
know if
my
syntax is wrong or if access doesn't like the case when statement.
Thanks
for
the thoughts.

'AssetClassSort' = CASE
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4
   WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5
  END

I also had END AS AssetClassSort


.