join expression not supported..run in Access 2003 but not in ADO ?

[Acie]

I'm writing a VB code with ADO to execute an sql that was copied from MS
Access 2003, after it was sucessfully tested. However, when I run this code
from an ADO routine, I got the above subject error message. It was run
agaisnt MS Acccess 2003 db.

Basically the query joins two tables (left join), checks if a curency is not
in USD and converts it and also includes a few other columns and stores it
into a new table.

Again this works fine when running it in MS Access 2003.

code sample below: code works fine & it is imbedded properly.
sql = "SELECT SEC_ID, ID_ISIN,
IIf(IsNumeric([EQY_DVD_HIST_ALL]),IIf([dvd_crncy]<> "USD" &
[exchange_rate]*[nbr2],[my_nbr]),Null) AS nbr_converted
Into mytable_temp FROM tbl1 LEFT JOIN tbl2 ON DVD = CODE;"

I have "USD" surounded by chr(34)...so it's fine.
....
cmd_sql.CommandText = sql
cmd_sql.Execute

Thanks for any ideas or thoughts ? Is it perhaps ADO that does not allow
this ?

 

[Sylvain Lafontaine]

ADO doesn't allow IIF() statements.