+ Reply to Thread
Results 1 to 2 of 2

SQL CASE statement on EXCEL VBA

  1. #1
    Gilson
    Guest

    SQL CASE statement on EXCEL VBA

    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.

  2. #2
    David Lloyd
    Guest

    Re: SQL CASE statement on EXCEL VBA

    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.


    "Gilson" <Gilson@discussions.microsoft.com> wrote in message
    news:E70032B0-58F0-4BD0-8CB8-F52DE79EC002@microsoft.com...
    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.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1