using CASE in access

[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

 

[John Spencer]

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

 

[alecgreen]

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 -

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

Alec

 

[XPS35]

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

Alec

How about using Help?

There I found:

Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5, inclusive.
Debug.Print "Tussen 1 en 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Tussen 6 en 8"
Case 9 To 10 ' Number is 9 of 10.
Debug.Print "Groter dan 8"
Case Else ' Other values.
Debug.Print "Niet tussen 1 en 10"
End Select

--
Groeten,

Peter
http://access.xps350.com


--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[John Spencer]

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

 

[alecgreen]

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

Alec- Hide quoted text -

- Show quoted text -

Brilliant! Thank you Guys

 

[David W. Fenton]

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 ...

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