How to do Case in a Query

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

I have a query with a field that will return about 100 different color
names. But for some of the names I want it to translate it into
something else. Keeping in mind that there are 100 or so colors it
can return and I only want to change a few, how is this to be done?

So if it returns this:

Black
Blue
Grey
Heather Gray
Blue
Green
Yellow
Yellow
etc.

I want code to change it to:

Blk (was Black)
Blue
Grey
Grey (was Heather Gray)
Blue
Green
Yell (was Yellow)
Yell (was Yellow)
etc.

I could nest them (but that will get hairy with five items) or I could
write a translating table. But is there a way to use the Case in a
query, like in VBA?

Thanks,

Matt
 
Create a table that has one row for each colour, and two columns: one for
"ColorFrom" and one for "ColorTo". Set ColorTo to ColorFrom for all those
colours you don't want changed.

Join that table to your existing query.
 
A Select Case statement is VBA.
Queries are not VBA.

With than many colors, an IIf statment would be unweildly, a Switch function
may not allow enough options.

Using a color coverter table and includinging in your query would be a good
iea.

One other approach would be to write a Public Function in a standard module
and put the Select Case statement in it and have it return the correct color.
To call a function from a query and have it execute on each record, use a
calculated field and pass the value of the color field to the function and
have the function return the converted color:

NewColor: ConvertColor([MyColorField])

The function (example only)
ConvertColor(strOriginalColor As String) As String

Select Case strOriginalColor
Case "Black"
ConvertColor = "Blk"
Case "Heather Grey"
ConvertColor = "Grey"
Case "Yellow"
ConvertColor = "Yell"
Case Else
ConvertColor = strOriginalColor
End Select
End Function
 
Note that if you go the function route (which I have no argument with), your
query will probably take a performance hit that it wouldn't if you used a
translation table (depends on how many records you are processing). One way
to maximize performance would be to try and order your Case statements in
the order of most-likely occurance (as much as possible). In Dave's example,
that would indicate that you know Black will occur more often than Heather
Grey or Yellow, etc. The closer any True condition is to the top of the
Select Case list, the faster the code will run. If the vast majority of your
conditions fall under "Case Else" then there is little you can do.

--
HTH,
George



Klatuu said:
A Select Case statement is VBA.
Queries are not VBA.

With than many colors, an IIf statment would be unweildly, a Switch
function
may not allow enough options.

Using a color coverter table and includinging in your query would be a
good
iea.

One other approach would be to write a Public Function in a standard
module
and put the Select Case statement in it and have it return the correct
color.
To call a function from a query and have it execute on each record, use a
calculated field and pass the value of the color field to the function and
have the function return the converted color:

NewColor: ConvertColor([MyColorField])

The function (example only)
ConvertColor(strOriginalColor As String) As String

Select Case strOriginalColor
Case "Black"
ConvertColor = "Blk"
Case "Heather Grey"
ConvertColor = "Grey"
Case "Yellow"
ConvertColor = "Yell"
Case Else
ConvertColor = strOriginalColor
End Select
End Function
--
Dave Hargis, Microsoft Access MVP


[email protected] said:
I have a query with a field that will return about 100 different color
names. But for some of the names I want it to translate it into
something else. Keeping in mind that there are 100 or so colors it
can return and I only want to change a few, how is this to be done?

So if it returns this:

Black
Blue
Grey
Heather Gray
Blue
Green
Yellow
Yellow
etc.

I want code to change it to:

Blk (was Black)
Blue
Grey
Grey (was Heather Gray)
Blue
Green
Yell (was Yellow)
Yell (was Yellow)
etc.

I could nest them (but that will get hairy with five items) or I could
write a translating table. But is there a way to use the Case in a
query, like in VBA?

Thanks,

Matt
Click to expand...
Click to expand...
 
Thanks to all. I was just wondering if I could avoid building the
table. I guess I can't. I could actually build a few Fields in the
query to perform the operation but that too is a bit noisy.

Thanks,

Matt
 
You could build a custom VBA function to handle this and then call the
function in the query. The reference table is usually a better solution -
easier to make changes, add colors, etc. And you don't have to list every
color, you can use an OUTER JOIN (LEFT JOIN or RIGHT JOIN) and then return
the original color when their is not value in the Reference table.

Field: WhatColor: Nz([RefTable].[NewColorValue],[OldTable].[Color])

A function would look something like the following UNTESTED code

Public Function ChangeColor(strColor)

Select Case strColor
Case "Yellow"
ChangeColor = "Yell"
Case "Black"
ChangeColor = "Blk"
Case Else
ChangeColor = strColor
End Select

End Function

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top