Replacing text in a field

[Mighty]

Hi Folks,

I'm sure this question has been asked before but the search function is down at the moment. I want to read data from a field in the database and replace the contents with something else depending on the content.

My query would be something like:

SELECT Replace(myTable.Buyer, 'A.N. Other', 'Joe Bloggs') as myBuyer ORDER BY Replace(myTable.Buyer, 'A.N. Other', 'Joe Bloggs')

The reason why I can't do:

SELECT IIF(myTable.Buyer = 'A.N. Other', 'Joe Bloggs', myTable.Buyer) as myBuyer

is that the 'A.N. Other' is only part of the field content - there is other text after it.

The Replace function doesn't appear to work. Is there any other way that I can do this??

Mighty

 

[hneal98]

You would need to use the left(), Mid(), or Right() functions along with the instr() function. You can look them up in Access Help to see how they work.

 

[Mighty]

I don't see how they will enable me to replace the text. I can use left, mid, right and instr to determine if the field contains A.N. Other - but how do I replace the text.

Mighty

 

[lespaul]

Use an update query:

UPDATE tblName set Somefield = Right(arg1, arg2) WHERE AnotherFIeld = Left(arg1, arg2)

Leslie

 

[Mighty]

Got it sorted:

SELECT IIF(isNull(myTable.Buyer), 'Joe Bloggs', IIF(inStr(myTable.Buyer, 'A.N. Other'), 'Joe Bloggs' & Right(myTable.Buyer, Len(myTable.Buyer)-10), myTable.Buyer)) as myBuyer
FROM myTable
ORDER BY IIF(isNull(myTable.Buyer), 'Joe Bloggs', IIF(inStr(myTable.Buyer, 'A.N. Other'), 'Joe Bloggs' & Right(myTable.Buyer, Len(myTable.Buyer)-10), myTable.Buyer))

Looks more complicated than it is......

Mighty

 

[lespaul]

that's not going to update any information or replace anything in your table though which is what your post indicated you wanted to do!

Leslie

 

[Mighty]

I never said I wanted to replace data in the table. I said that I wanted to READ data from a table and replace the contents - that is why I was using a SELECT query.

Mighty

 

[PHV]

In a standard code module paste this code:

Public Function myReplace(myString, myFind As String, myRepl As String)
If Len(Trim(Nz(myString & ""))) > 0 Then
  myReplace = Replace(myString, myFind, myRepl)
End If
End Function

Then your query may be like this:
SELECT myReplace(myTable.Buyer, 'A.N. Other', 'Joe Bloggs') as myBuyer ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I want to read data from a field in the database and replace the contents with something else depending on the content.

I guess the above comment wasn't stated clearly enough then. Glad you got it all worked out.



Leslie