CASE statement keeps blowing up.....

[myk]

Hello All,

I created a table in Access based upon some Oracle tables. I now want
to do some more complex querying against the Access table using SQL.
One of the statements I would like to use in this is the CASE
statement, yet I keep getting Syntax Errors when I try to run the query
that contains the CASE statement. Is the statement not recognized by
Access? When I run a similar query as a Pass Through query it runs
fine.

TIA,

Mike

 

[John Spencer]

Access SQL does not support Case statements

You can use IIF, Switch, Choose functions in the SQL statement OR you can
write a custom function in VBA.

IIF statements can be nested.

See help for further information, or post back with a specific case
statement and someone should be able to help you rewrite for Access.

 

[chris.nebinger]

NOTE:

If you have the Oracle tables linked, and you run an Access query on
them, you have to bring all the records from Oracle to Access for
processing. This is much slower than executing a Pass-Through query.
The Pass-Through query does not process the SQL, so you would use
Oracle's flavor of SQL vs. Access's. The query also only returns the
records that meet your criteria. The Access Help has more information.


Chris Nebinger

 

[John Spencer]

Perhaps I misunderstood, but I thought the poster's comment "created a table
in Access based upon some Oracle tables..." made it clear that the query
needed to be an Access SQL compatible query. Using a pass-through query to
Oracle would be fine if the tables were still in Oracle.

I'm not so sure that a query to an external database (ORACLE or MS_SQL using
ODBC) will bring all the records to Access for processing. I think it
depends on the query, the indexes available, the presence (or absence) of
custom user functions and other factors. However, I don't know since I have
no "access" to the underlying code.