Renaming query values found using like statement

[gixer]

Hi,

I'm looking to to rename values returned in my MS Access query.

Example table data:

Companyname
Juice Inc
Juice Incorporated
Juice Corporation
Juice Corp
Heat Ltd
Focus Corporation

I thought I could use the Switch function:

Switch([Raw Data]![Companyname] Like "*Juice*","Juice Inc")

But it only returns the value of "Juice Inc" and the other company names (Heat Ltd, Focus Corporation)are not returned.

I have about 30 company names that need to be standardized, so there should be no limitation on the number of values that need to be renamed.

Does anyone know how to tackle this problem?

Thanks!!

 

[dinger2121]

is this something you are going to have to continually do?
Your best bet will probably to build a vba function to handle the changes.
You could do this with an update query using IIF, but that can get very confusing.

 

[gixer]

Once it's initally setup, it won't change too often. I could write an update query, but I don't want to change the data in the table. I'd rather just use a select query to change the representation of the values I deem needing to be changed.

Also, the query values need to be found using a like condtion, and not something like

Switch([Raw Data]![Companyname] ="Juice Corporation","Juice Inc")

 

[dinger2121]

Creating a function in vba seems to be the better option. Then it can be reused in queries going forward without changing any of the original data.

 

[gixer]

What would the code look like for this? I've never created a VBA function.

Thanks

 

[dinger2121]

something like this could probably work...I just threw this together quickly so it may need some work.

Function renameCompany(myName)
Dim tmp
Dim name

If (Left(myName, 5) = "Juice") Then
tmp = "Juice Inc"
ElseIf (Left(myName, 5) = "TEST1") Then
tmp = "Something else"

End If

renameCompany = tmp
End Function

 

[gixer]

Okay thanks, I'll try that.