Using A CASE Statement

fastcorvette · December 18th, 2003, 09:46 AM

What is the correct syntax for using a CASE function in a SQL statement that has 4 cases to evaluate when running against an Access 2000 database? In VB the CASE would be as follows:

SELECT CASE Plan
     CASE "MON"
          SupPlan = Amt
     CASE "QUR"
          SupPlan = Amt * 4
     CASE "Ann"
          SupPlan = Amt * 12
     CASE "NON"
          SupPlan = 0
END SELECT

How do I do this same thing with SQL in Access?

jemacc · December 18th, 2003, 12:25 PM

In SQL:

DECLARE @TestVal int
SET @TestVal = 3

SELECT
    CASE @TestVal
        WHEN 1 THEN 'First'
        WHEN 2 THEN 'Second'
        WHEN 3 THEN 'Third'
        ELSE 'Other'
    END

fastcorvette · December 18th, 2003, 12:30 PM

I can't declare variables in a SQL statement that is going to be used against an Access database. The syntax I am looking for will have to be one long line that can be run against the Microsoft Jet 4.0 engine.

edcaru · December 21st, 2003, 06:53 AM

Why don't you put your code in a function within a Standard module and invoke it from a query or SQL statement?

fastcorvette · December 24th, 2003, 01:56 AM

I didn't know you could access a VB function with SQL in Access 2000. How do you do that? I have no idea how to create a SQL statement that would reference a function in a VB module.

joefawcett · December 24th, 2003, 01:39 PM

Go to modules, add new and put in a public function:

Code:

Public Function GetNow() As Date

  GetNow = Now()
End Function

This one duplicates an existing function but you can do more or less anything.

--

Joe