Creating new field to table

[AccessTino]

Hello Everyone,

I'm trying to create a new field(2) based on another field(1) in my query.

The goal is to replace words in field(1) and display them in field(2).


So for example I have the word "apple" that is in field(1) and needs to be replace with orange in field(2). And if no replacement can be done just show the original value from field(1).

I have like 5 words that need to replaced in field(1)

I think it is just like a calculated field but i tried going that way with the expression builder but I cannot get it to work with the replace function. As i'm new to this i'm probably doing something wrong.


(I cannot edit the original table to reflect what I'm trying to achieve)


Also i need to be able to add multiple values to the formula to be able to replace the 5 words in field(1) and be able to show them in field(2)

so the original table stays intact but the new table just has formatting done to it.

As I'm new to access (2010) and cannot write VBA or SQL I'm hoping someone can show me an easy way to achieve this via the expression builder in combination with a query or report.



All help is welcome!!! And thanks in advance!!

[isladogs]

This will work for the specific example you gave either as an extra field in a SELECT query or (without the Field2 in the Update To row of an UPDATE query

Field2: Replace([Field1],"apple","orange")

To do 5 changes, either create nested Replace functions - easy to go wrong or create a user defined function to do each of these
BUT I have to ask .... why?

[AccessTino]

Hi Ridder52,

Thanks for your quick reply you solution works perfectly for one value.

The reason we are wanting to change multiple values is that Field1 has sub categories and we need main categories. (so apples belong to fruit but carrots belong to vegetables )

For reporting functionality this is way better for us as we can export the data into an excel already formatted. Unfortunately we are unable to change the original tables because that would be a quick fix. And we don't want to edit this in excel because that will take up so much time everytime we want a report.

If you have another solution we are open for that of course but using VBA is far out of our comfort zone. I'm aware I can group this categories in the pivotview but that's not wat we are trying to achieve.

[isladogs]

There are several solutions as with everything in Access. These include:

1. Nested Replace
Replace(Replace(Replace(Replace(Replace([Field1], "apple", "fruit"), "pear", "fruit"), "carrot", "vegetable"),"cabbage","vegetable"),"plum","fruit" )

2. Nested Switch
Switch([Field1]= "apple", "fruit", [Field1]= "pear", "fruit", [Field1]= "carrot", "vegetable",[Field1]= "cabbage","vegetable",[Field1]= "plum","fruit")

3. Use a lookup table with fields Field1 & Field2 then populate the table with records like apple / fruit etc
Then use DLookup("Field2", "tblLookup", "Field1='" & [Field1] & "'")

4. A user defined function - as you aren't confident with VBA, I'll omit that for now

Methods 1 & 2 will both work but are messy. If you want to add a new item like avocado you have to alter the function with the additional item
Method 3 is by far the simplest and easiest if you need to edit the items later

[NoellaG]

Why not get the correct data in the base query for the data? If you have categories and subcategories you probably have a table categories and a table subcategory, link the tables in a query and get the correct fields from the data you want. If it is as difficult to get the correct data, maybe you should review your data structure.