Replace & with a space 1

[Bennie47250]

Using Access 2003

I have a text string that MAY include the & character. If the & exist, I need to replace it with a space. For example, “LATCH CABLE SPO/A7/9EH1&2” would be updated to “LATCH CABLE SPO/A7/9EH1 2”

I would like to do this in a Query or a Macro, is this possible? If yes, please give me an example.

Given what we had sold for a day, it is possible that none of the records will have an & or maybe only 1 out of 20 will.

Thanks
Bennie

 

[HarleyQuinn]

Yep, no problem. Have a look at the Replace() function.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Bennie47250]

I will look at it again. First time I tried, it replaced the entire string LATCH CABLE SPO/A7/9EH1&2 with the space.

 

[HarleyQuinn]

I can only guess that would be to do with the ordering of the parameters passed to the function. Try using the Replace() function like this:

Replace(yourfield,"&"," ")

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Bennie47250]

If at first you don't succeed, try, try again. Thanks Harley, that worked exactly as needed.

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.