using CASE in access

  • Thread starter Thread starter alecgreen
  • Start date Start date
A

alecgreen

Hi

I am trying to understand the use of CASE statement in access, can
anyone please give me an easy to understand example, with how to call
it within a query?

Many Thanks

Alec
 
Case statement is not supported in Access SQL.

Case can be used in VBA procedures.

In queries use can use nested IIF statements.

Or you can use the VBA functions Switch and Choose.

Or you can build a custom VBA function that returns a value based on the use
of the VBA Case structure.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Case statement is not supported in Access SQL.

Case can be used in VBA procedures.

In queries use can use nested IIF statements.

Or you can use the VBA functions Switch and Choose.

Or you can build a custom VBA function that returns a value based on the use
of the VBA Case structure.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County







- Show quoted text -
Click to expand...

Hi, Thanks for the response - how about an example using VBA then
please

Alec
 
Here is a simple VBA function

Public Function fReturnAValue(vIn)

SELECT Case vIn
Case "A"
fReturnAValue= "Abalone"
Case "S"
fReturnAValue= "Swordfish"
Case "T"
fReturnAValue= "Tuna"
Case Else
fReturnAValue= vIn
END SELECT

End Function

In a query you could call that using
SELECT fReturnAValue([SomeTable].[SomeField]) as FishType
FROM ...

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Here is a simple VBA function

Public Function fReturnAValue(vIn)

   SELECT Case vIn
      Case "A"
         fReturnAValue= "Abalone"
      Case "S"
         fReturnAValue= "Swordfish"
      Case "T"
         fReturnAValue= "Tuna"
      Case Else
         fReturnAValue= vIn
   END SELECT

End Function

In a query you could call that using
SELECT fReturnAValue([SomeTable].[SomeField]) as FishType
FROM ...

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


Hi, Thanks for the response - how about an example using VBA then
please
Click to expand...
Alec- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Brilliant! Thank you Guys
 
Here is a simple VBA function

Public Function fReturnAValue(vIn)

SELECT Case vIn
Case "A"
fReturnAValue= "Abalone"
Case "S"
fReturnAValue= "Swordfish"
Case "T"
fReturnAValue= "Tuna"
Case Else
fReturnAValue= vIn
END SELECT

End Function

In a query you could call that using
SELECT fReturnAValue([SomeTable].[SomeField]) as FishType
FROM ...
Click to expand...

But that's storing data in code -- that's a lookup table.
 
Back
Top