SQL CASE statement on EXCEL VBA

[Guest]

Hello folks ! How can I use CASE WHEN statement on excel vba ? I'm using
excel sheet like database I did :

dim db as dao.database
dim rs as dao.recordset
dim cSQL as string

set db=opendatabase(thisworkbook.path & "\" &
thisworkbook.name,false,false,"Excel 8.0")

cSQL="select cd_codigo, (case when cd_hist=1 then valor else 0 end), (case
when cd_hist=2 then valor else 0 end) from [Plan1$]"

set rs=db.openrecordset(cSQL)

but this code make a error. Why ?

regards.

 

[David Lloyd]

My understanding is that the CASE statement is supported for SQL Server
T-SQL. DAO, of which the OpenDatabase method is a part, is developed for
JET workspaces as well as ODBCDirect workspaces. The default for DAO is a
connection to a JET datasource. JET SQL syntax does not support the CASE
SQL statement. One alternative would be to use an IIF statement instead.
For example:

IIF(cd_hist=1, valor, 0)

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello folks ! How can I use CASE WHEN statement on excel vba ? I'm using
excel sheet like database I did :

dim db as dao.database
dim rs as dao.recordset
dim cSQL as string

set db=opendatabase(thisworkbook.path & "\" &
thisworkbook.name,false,false,"Excel 8.0")

cSQL="select cd_codigo, (case when cd_hist=1 then valor else 0 end), (case
when cd_hist=2 then valor else 0 end) from [Plan1$]"

set rs=db.openrecordset(cSQL)

but this code make a error. Why ?

regards.