Select Case in Update Query?

[HenryAnthony]

Hi,

I have a list of strings that I need to update to to different values if they meet certain conditions and need a jump start to get my brain around this. For instance:

If field 1 = scarlet then field 2 = red
If field 1 = red then field 2 = red
If field 1 = turquois then field 2 = green
If field 1 = green then field 2 = green

and so on with about 120 different pairings.

I know this should be done using Select Case in an Update Query but can't quite get thing working. Any help is greatly appreciated. Thanks in advance.

Best regards,

Henr¥

 

[smedvid]

Perhaps a better approach is to build a database table with the cross reference... Field1, PresentColor, NewColor.

Then add a new column to the table to store the NewColorCode. Next, write an Update query to populate the NewColorCode field. then rename the fields and delete the old color one... Asusming this is a one time deal, that is how I would do it. Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[jebry]

Hi Henry!

Select Case is not available in SQL. You can write a public function and use that function in the query:

Public Function ChangeColor(OrigColor As Variant) As String

Dim NewColor As String

Select Case OrigColor
Case "Red", "Rose"
NewColor = "Red"
Case "Blue", "Midnight Blue"
NewColor = "Blue"
etc.
End Select

ChangeColor = NewColor

End Function

Then you could call it in the SQL sending it the field in question.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[HenryAnthony]

Well DUH! This I can do in a heartbeat! The table is already in Excel. Thanks for pointing me in the right direction. I still want to learn that "Case thing" though.

... Asusming this is a one time deal.

Actually, this is not a one time deal - although this will get me through my meeting. I will need to turn this over to non-Access users for periodic updates.

Best regards,

Henr¥