Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replacing text in a field

Status
Not open for further replies.
Mighty

Mighty

Programmer
Feb 22, 2001
1,682
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Use an update query:

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

Leslie
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
In a standard code module paste this code:
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
 
Upvote 0 Downvote
I want to read data from a field in the database and replace the contents with something else depending on the content.
Click to expand...

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



Leslie
 
Upvote 0 Downvote
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top