REPLACING TEXT STRINGS

[vlitim]

Hi

I have a "Name" field in access which contains product names, but this field has a lot of repetitive text in it i.e. "FORD AUTOMOBILES ESCORT"

On my report I want to eliminate the repetitive text i.e. "FORD AUTOMOBILES" displaying only the variable i.e. "ESCORT".

I can do this in Excel with SUBSTITUTE i.e.
SUBSTITUTE (cell,"FORD AUTOMOBILES","&quot
but cannot find any way to do this in Access using the report or supporting query,

Help much appreciated!

Thanks

 

[crose7227]

Update Query!

 

[vlitim]

Crose

I don't want to change the field permanantly, just remove the text string for report purposes. The full string must be kept intact in the "name" field,

 

[crose7227]

switch function!

 

[CosmoKramer]

I think this will work. Set up an expression field in your query something like this:

ModelOnly: Right(Trim([Automobile]),Len(Trim([Automobile]))-InStr(InStr (1, [Automobile]," ")+1,[Automobile]," "))

Just replace your field name and refer to this field in your report.....

 

[crose7227]

You lost me there KosMo

 

[Bagger]

use this example uses the Left function to return a specified number of characters from the left side of a string.

Dim AnyString, MyStr
AnyString = "Hello World" ' Define string.
MyStr = Left(Automobile, 17) ' Returns "Escort".

 

[CosmoKramer]

This will create a temporary field in the query. vlitim's request was to retain the original field, but only display the car's model in the report. Referencing this expression field in the report should do just that......

 

[Bagger]

Disregard last Message, my mistake

use this example uses the Left function to return a specified number of characters from the left side of a string.

Dim AnyString, MyStr
AnyString = "FORD AUTOMOBILES ESCORT" ' Define string.
MyStr = Left(Automobile, 17) ' Returns "Escort".

 

[CosmoKramer]

Bagger - Your example would return "FORD AUTOMOBILES " not "ESCORT". The RIGHT function would work by itself, but only if there was a fixed number of characters to account for......