'Select Case' problem

[Willem!]

Hi,

I have a query which contains many nested IIF-statements. Due to that, my database is running terribly slow. I want to use a 'SELECT CASE' statement because I think this would be faster.

In order to get used to the SELECT CASE-thing I created a new database and I implemented a query with the following SQL-code:

SELECT ColorID, 'Colorvalue' =
CASE
WHEN Color = 'Green' THEN 100
ELSE '0'
END
FROM Color.Colors

(Color is a fieldname, Colors is a tablename)

This code is not working, between CASE and WHEN is something wrong according to Access.

Probably I am overlooking the obvious, does anyone has a tip for me??

Cheers,
Willem

 

[selenau837]

I'm not sure what you are trying to do, but here is an example of a correct Select case statment. I can't follow the way yours is set up. Hopefully below will help you set yours up correctly.

Dim Number
Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8    ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
Case 9 To 10    ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else    ' Other values.
    Debug.Print "Not between 1 and 10"
End Select

 

[Willem!]

Hi,

for the record, I am trying to build a 'select case' statement in a query in SQL. I think the code you've written is VBA-code.

If I want to use 'Dim', Access is giving me an error 'invalid SQL-statement'.

Is my problem clearer now?

 

[TanisAgain]

What exactly are you trying to change the colour of? Background of a control? Foreground of some text? Colour change on the result of a test? Some detail might help.

 

[selenau837]

Hi,

for the record, I am trying to build a 'select case' statement in a query in SQL. I think the code you've written is VBA-code.

If I want to use 'Dim', Access is giving me an error 'invalid SQL-statement'.

Is my problem clearer now?

That was an example out of the helpfiles in access.

If you are using it in SQL, then I am unable to help you. I'm not sure how to proceed.
My apologise for not initially understanding your question. I'm sure someone will come by and help soon.

 

[Willem!]

Aah, I can see my you do not understand my problem.

I do not want to change colors or whatsoever, I've build a meaningless database that has a table with 'ColorID' and 'Color' and I want to make a query that shows all the ColorID's and when a Color = Green it displays '100' or else '0'.

I did this in order to understand the select case statement.


However, as said in my first message in this thread my real problem is a bunch of nested IIF-statements. IIF(...;..;IIF(...;.... etc..etc...
I'm trying to transfer those IIF's into SELECT CASE-statement, in SQL, not VBA.

Does this clearify my problem?

 

[Willem!]

Thanks anyway Sel!

 

[RuralGuy]

You can *not* use a Select Case statement in an SQL statement directly but you can create a User Defined Function in a standard module that uses the Select Case structure and returns the value you want.

 

[Willem!]

Hmm, thanks, I was confused due to some publications on some other forums where apparantly it WAS possible to do such a thing.

Perhaps those where meant in a slight different context (http://msdn2.microsoft.com/en-us/library/ms181765(SQL.90).aspx), I read global through the articles, so probably I read over something.

However, thanks RuralGuy for pointing me in the right direction, this should give me enough info to complete my query.

Cheers,
Willem

 

[RuralGuy]

Your reference is for creating scripts that operate SQL Server directly. It is a little confusing but that language it *very* different than the SQL syntax allowed in Access queries.

 

[selenau837]

RG to the rescue once again!!

 

[RuralGuy]

Hi Sel,
Just thought I'd jump in for a bit when you started having a little trouble.

 

[selenau837]

Hi Sel,
Just thought I'd jump in for a bit when you started having a little trouble.

No, I'm glad. I was worried the thread count would be high and noone would jump in. Some people tend to stay away form threads when the count is high, thinking the person is getting help.

You are wonderful RG!!! You, Pat, SJ, and Ghudson are the absolute best at this. There are more I'm sure, but y'all are the ones that stick out the most.



PS..so when is my check in the mail for this great endorsement.

 

[RuralGuy]

Thanks for the endorsement.

PS..Shhhh, don't tell everyone!

 

[selenau837]

Thanks for the endorsement.

PS..Shhhh, don't tell everyone!

OK!!

 

[Willem!]

Then I will conclude this thread with a hurray for the Access-specialists in this forum!

Hurray! Hurray! Hurray!

Willem