[thelist] Front End Access access to SQL DB's

[rudy]

> In fact there were 4 columns of nested IIF statements using
> linked tables to an SQL server and the queries ran fine.
> 
> Can anyone explain this? 

yes, it's simple

when you link tables in access, access gets rows from them 
using odbc or whatever to connect to them, but the actual
processing of rows, columns, joins, and functions such as IIF, 
is all done inside access using access syntax

this is why you can write a query to join a linked oracle table
to a linked sql server table in access -- such a join can 
potentially be very inefficient, as the request for rows from 
each table can utilize only row filters pertaining to that table, 
but nevertheless, if you have to join tables across separate
databases, access can be a lifesaver

in your case, you have linked sql server tables, but the IIF
function has to be access syntax

change the query to a "pass-through" query, however, and you
will quickly see the difference -- now the sql is merely handed 
over to sql server, and must conform to sql server syntax


rudy