Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL SELECT CASE in Access

Discussion in 'Information Technology' started by Edmund, Jul 28, 2006.

  1. Edmund

    Edmund
    Expand Collapse
    Guest

    Hello,

    Does Access have an equivalent command to SQL Sever's SELECT CASE that can
    be used in queries?

    Thanks

    Edmund
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Edmund" <die_spambot@hatespam.com> wrote in message
    news:uUhW6ujiGHA.836@TK2MSFTNGP02.phx.gbl
    > Hello,
    >
    > Does Access have an equivalent command to SQL Sever's SELECT CASE
    > that can be used in queries?


    There are VBA functions Switch() and Choose() that can be used in
    queries.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Yes. and it called IIF

    So, if you have that in SQL
    Case FieldName
    When 1 Then "One"
    Else
    "All"
    End As NewFieldName

    In Access you'll write it as
    IIF([FieldName] = 1 , "One","All") As NewFieldName

    --
    Good Luck
    BS"D


    "Edmund" wrote:

    > Hello,
    >
    > Does Access have an equivalent command to SQL Sever's SELECT CASE that can
    > be used in queries?
    >
    > Thanks
    >
    > Edmund
    >
    >
    >
     
  5. Edmund

    Edmund
    Expand Collapse
    Guest

    Thanks, I did not know that VBA functions could be used in Access queries.

    Edmund


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:%23aciu0jiGHA.412@TK2MSFTNGP05.phx.gbl...
    > "Edmund" <die_spambot@hatespam.com> wrote in message
    > news:uUhW6ujiGHA.836@TK2MSFTNGP02.phx.gbl
    >> Hello,
    >>
    >> Does Access have an equivalent command to SQL Sever's SELECT CASE
    >> that can be used in queries?

    >
    > There are VBA functions Switch() and Choose() that can be used in
    > queries.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  6. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Edmund wrote:
    > Thanks, I did not know that VBA functions could be used in Access
    > queries.


    If you call them from within Access you can. If you were running queries
    from some other program you would not be able to. Access has an expression
    service that resolves them.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  7. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
    news:dzEhg.40106$4L1.8607@newssvr11.news.prodigy.com
    > Edmund wrote:
    >> Thanks, I did not know that VBA functions could be used in Access
    >> queries.

    >
    > If you call them from within Access you can. If you were running
    > queries from some other program you would not be able to. Access has
    > an expression service that resolves them.


    Actually, I'm pretty sure you can call built-in VB functions in queries
    even if they're run from outside Access (provided that the VB runtime is
    installed on the PC). I tested that at one point. But you can't call
    user-defined VBA functions unless the query is run within Access.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  8. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
    > news:dzEhg.40106$4L1.8607@newssvr11.news.prodigy.com
    >> Edmund wrote:
    >>> Thanks, I did not know that VBA functions could be used in Access
    >>> queries.

    >>
    >> If you call them from within Access you can. If you were running
    >> queries from some other program you would not be able to. Access has
    >> an expression service that resolves them.

    >
    > Actually, I'm pretty sure you can call built-in VB functions in
    > queries even if they're run from outside Access (provided that the VB
    > runtime is installed on the PC). I tested that at one point. But
    > you can't call user-defined VBA functions unless the query is run
    > within Access.


    Ah, good to know.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  9. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > Actually, I'm pretty sure you can call built-in VB functions in queries
    > even if they're run from outside Access (provided that the VB runtime is
    > installed on the PC).


    This is one of those times when you need to understand where Access
    ends and Jet begins <g>.

    Broadly speaking, Jet 4.0 implements the VBA5 functions (not methods)
    that return scalars (single values e.g. not arrays). I guess the Jet
    team never got around to implementing the VBA6 functions (or was Jet
    4.0 code-complete before VBA6?). Most sorely lacking in Jet 4.0 is a
    REPLACE$ function :(

    Therefore, if your Query contains VBA6 functions it will fail if not
    executed via Access.

    In lieu of anything official from MS, Brendan Reynolds was compiling a
    definitive list (http://brenreyn.blogspot.com/).

    Jamie.

    --
     

Share This Page