DAO, SQL Case Statement

[ghislain007]

Can anyone show me the correct code to perform a Case When statement using DAO 3.6 object library?
I've tried a couple of things but with no results:
ex: Select case [field1] when > 1 then 'OK' end

Thanks for your help

[Hack]

Welcome to the forums.

It looks to me like you have it right. This is how I would write it.

SELECT CASE WHEN (Field1 BETWEEN somevalue AND someothervalue) THEN
ELSE somedefaultvalue
FROM yourtable;

[ghislain007]

Hi Hack,
Thanks for your quick reply.

This is driving me crazy!!! I always get runtime error 3075.
Is it possible that MS DAO 3.6 doesn't suport the select case statement ?!
Here's anther example:
Set RS = db.OpenRecordset("Select Case When ([01_INFO].[Index1] ='XXX') then '11' end from [01_INFO]")

[techgnome]

The SQL Syntax for CASE statements looks like this:

CASE Expression
WHEN Value THEN Result_Expression
WHEN Value THEN Result_Expression
ELSE Result_Expression
END

Hack's example is just missing the END portion.

In ghislain007's example, the WHERE clause is simply in the wrong spot.
Select case when [field1] > 1 then 'OK' end


-tg

[si_the_geek]

It isn't necesarily about DAO supporting it, it may well be the database that doesn't support it. Which database system are you using? (Access, SQL Server, etc)

There are a couple of things in your last SQL statement that dont quite look right to me (but I could be wrong!), I dont think you need the brackets, and you should ideally have an alias for your 'calculated' field, eg:

Set RS = db.OpenRecordset("Select Case When [01_INFO].[Index1] = 'XXX' then '11' end myField from [01_INFO]")

[Hack]

The SQL Syntax for CASE statements looks like this:

CASE Expression
WHEN Value THEN Result_Expression
WHEN Value THEN Result_Expression
ELSE Result_Expression
END

Hack's example is just missing the END portion.

In ghislain007's example, the WHERE clause is simply in the wrong spot.
Select case when [field1] > 1 then 'OK' end


-tg

Arrrrrrrrrrrrrrrrrrrrgggg...you know what TG, I'm constantly forgetting that stupid END when I do it for real to! *someone just shoot me*

[ghislain007]

It isn't necesarily about DAO supporting it, it may well be the database that doesn't support it. Which database system are you using? (Access, SQL Server, etc)

There are a couple of things in your last SQL statement that dont quite look right to me (but I could be wrong!), I dont think you need the brackets, and you should ideally have an alias for your 'calculated' field, eg:

Set RS = db.OpenRecordset("Select Case When [01_INFO].[Index1] = 'XXX' then '11' end myField from [01_INFO]")

I'm using MSAccess (Access 2000 file format)
Here's more info on my code:
dim db as database
set db = DBEngine.OpenDatabase(filename)
dim rs as recordset

I tried removing the brackets but I alwas get an error because the names of the tables and fields that I read are often compose of 2 words (Product Code).


Considering everyone's comment, I modified my query to this :
Set RS = db.OpenRecordset("Select CASE WHEN [Index1] = 'XXX' THEN 'OK' End from [01_INFO]")


.... but I still get an error message.


I thank you all for your help.

[dannymking]

If this is Access I don't think the CASE statement is available against the JET engine.. you might want to take a look at the IIF Statment instead..

Select (IIF([Index1] ='XXX','11',[Index1])) As Expr FROM [01_INFO]")

[si_the_geek]

Correct, Access does not support the Case statement. IIF/IF is the way to go

[ghislain007]

Problem Sloved!!!!!!!

Danny is right Acces doesn't support the case statement.



Thanks again!!!