does case syntax exist in Access?

[Ed]

Hi:
Is it possible to use TSQL CASE syntax in an Access query?

I'm trying to create a cartesion bewteen two tables -
table one with three rows per id1 and table two with
id2. Then for each case of id1 get a different column
from table two referenced by id2

Thanks

 

[Duane Hookom]

No, but you can use IIf(), Switch(), and Choose().

 

[Tom Ellison]

Dear Ed:

Access has two available database engines that ship with the product:
Jet and MSDE. MSDE is a version of SQL Server and accepts the CASE
statement perfectly well. Jet won't do this, and the alternatives can
be quite messy. Sorry!

Perhaps you think of Jet as being Access, as it has been a part of
Access for ever, while MSDE was introduced with Access 2000. But, for
those of us who use Access exclusively with MSDE, we hardly think of
Jet being Access and MSDE not being Access. I think this is a pet
peeve! However, with a complete design environment for MSDE having
been added in Access 2000, that being the Access Data Project (ADP) it
can hardly be said that Jet is Access and MSDE is not.

If you're accustomed to using TSQL but are working in an MDB, you can
write pass-thru queries to MSDE as well as writing Jet queries on
linked tables, and get the best of both worlds. The tables can be in
either Jet or MSDE, as MSDE can contain linked servers of Jet
databases.

Hi:
Is it possible to use TSQL CASE syntax in an Access query?

I'm trying to create a cartesion bewteen two tables -
table one with three rows per id1 and table two with
id2. Then for each case of id1 get a different column
from table two referenced by id2

Thanks

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Alick [MSFT]]

Hi Ed,

Duane and Tom have provided the answer to the question: it is not possible
to use TSQL CASE in Access Jet engine, you can use IIf(), Switch(), and
Choose() as Duane said, I just want to post the related articles for your
reference if you have any interest since Tom mentioned MSDE.

Chapter 1: Understanding Microsoft Access 2000 Client/Server Development
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnapg/html/
apg01.asp

Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An
Alternative to Jet for Building Desktop and Shared Solutions
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html
/msdeforvs.asp


Please feel free to reply to the threads if you have any questions or
concerns.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Content-Class: urn:content-classes:message
| From: "Ed" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi:
| Is it possible to use TSQL CASE syntax in an Access query?
|
| I'm trying to create a cartesion bewteen two tables -
| table one with three rows per id1 and table two with
| id2. Then for each case of id1 get a different column
| from table two referenced by id2
|
| Thanks
|

 

[Ed]

Since I'm trying to do this in a make table query and
select one of three columns depending on a selected value
from another table I couldn't see how this would work. Do
you have an example?

 

[Ed]

Thanks Tom!

I went down this path - but to speed up all the reports
several years ago we had created make table queries that
got run right before the reports.... So as a TSQL guy I
loaded MSDE on a machine and converted all the data over
and then kicked off the reports which ran slower than
dried mollasses as Access queries against MSDE... I would
have to rewrite 80 or so queries as stored procs to go to
an ADP. It's a neat little app we sell to school systems.
I can create the table and update it as worst case for
now.

 

[Duane Hookom]

IIf([YouProvideYourCaseSyntax], [SomeoneCanProvideIIfSyntax],
[SomeoneWillHaveToGuess])
You can nest IIf()s within IIf()s.
IIf([YourExpressionNestsSeveralLevels], [TrySwitch], [StayWithIIf])

 

[Alick [MSFT]]

Hi Ed,

Do you have any question to implement the iff function into your
application? If so, please feel free to provide the detailed situation on
your side, so I can create a sample for your.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Content-Class: urn:content-classes:message
| From: "Ed" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.queries
|
| Since I'm trying to do this in a make table query and
| select one of three columns depending on a selected value
| from another table I couldn't see how this would work. Do
| you have an example?
| >-----Original Message-----
| >No, but you can use IIf(), Switch(), and Choose().
| >
| >--
| >Duane Hookom
| >MS Access MVP
| >
| >
| >| >> Hi:
| >> Is it possible to use TSQL CASE syntax in an Access
| query?
| >>
| >> I'm trying to create a cartesion bewteen two tables -
| >> table one with three rows per id1 and table two with
| >> id2. Then for each case of id1 get a different column
| >> from table two referenced by id2
| >>
| >> Thanks
| >
| >
| >.
| >
|