MSAccess ODBC

[tonyweis138]

I am writing trying to access data in an Access database via a Delphi 7 program. I have a DSN setup using the Microsoft Access driver which i am using to connect to the Access database.

I am attempting to use a query like the following and it keeps giving me read access errors. It has to do with the case statement (i have tried rewriting it etc.) because if i take it out it works fine. Any ideas

select *, case when codamount > 100 then 'high' else 'low' end as CODTYPE from UPS_MAIN_EXPORT_COPY_TBL where SalesOrderNo = user input

 

[genomon]

case when codamount > 100 then 'high' else 'low' end

Not completely certain, but while this will work with T-SQL, it may not be Jet SQL compliant. You may want to take a look in Access help, and see if you can code an Immediate If (IIf) statement.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[tonyweis138]

I tried using iif and it still gives me an access violation when i try to run the query in Delphi.

 

[tonyweis138]

According to this:

http://support.microsoft.com/kb/321266

Neither the if(f) nor the case statements are valid. Does anyone know how to achieve the same goal using only these keywords?

Thanks in advance.

 

[tonyweis138]

Haha, i'm a doofus. I had IFF in my brain for if and only if and kept using that instead of iif.

Also, because i was using version 12 of the Microsoft Access Driver, i don't think the BDE was able to interpret the error messages correctly so it was just giving me read access violations instead of useful info.

Thanks for the help.

 

[genomon]

select *, IIf([codamount] > 100,"high","low")  as CODTYPE from UPS_MAIN_EXPORT_COPY_TBL.....

Works fine for me in Access.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."