Find Character In A String And Replace

[dtaylor]

hello all!
is there a way to find a character in a string ,maybe like the FIND function in excel, and replace with a diff character?

my problem is the the placement of the character will be differenct from record to record (and i have 750k records).

here is a sample of the text string residing on a table:
OLD (04/01 RUDOLPH, DON)

i need to take out the forward slash and replace it with a dash.

i need this automated since my whole process with this is automated. i need the character to replaced so it can be exported to excel and excel doesn't like '/' in a file name.

i have been playing with (vba) if statements and update sql statements but cannot nail down the position of the '/'.

any help would be greatly appreciated!

thank you,

dan

 

[dtaylor]

Hi All - Found what I was looking for in this post..

http://www.mrexcel.com/board2/viewtopic.php?t=60178&highlight=character+position

 

[mdmilner]

Hmmm, noticed my own contribution to that quoted thread with a typo in the code submission. Learned a lot just in the last few months - would never have attempted to use the various Left/Right/Mid functions to do this now. But the below works just fine within VBA.

My first attempt to drop replace into an update query gave me an unknown function. Thinking there has to be an easy SQL way.

Sub replaceit()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb

strSQL = "SELECT * FROM tblName"
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
With rs
 Do Until rs.EOF
  .Edit
  .Fields(0).Value = Replace(.Fields(0).Value, "/", "-")
  .Update
  .MoveNext
 Loop
End With

End Sub

Mike

 

[dtaylor]

Hey - thanks for the info....
after posting the link at home last night I discovered that it was not going to do all i wanted....

I ended up using the Mid function. I finally realized the word 'OLD' and the date where in the same position on all records with these entries.

So I ended up slicing and dicing the record and putting it back together..
Here is a sample of what I did:

Do While Not rst.EOF
If Left((rst.Fields("SALESPERSON NAME")), 3) = "OLD" Then
var = Len(rst.Fields("SALESPERSON NAME")) - 11
'11 is the char position where the date ends.

fldBg = "(OLD "
fldMd = Mid(rst.Fields("SALESPERSON NAME"), 6, 2) & "-"
fldMd = fldMd & Mid(rst.Fields("SALESPERSON NAME"), 9, 2)

fld = fldBg & fldMd & " " & Trim(Right(rst.Field("SALESPERSONNAME"), var))

rst.Fields("SALESPERSON NAME") = fld
End If

rst.MoveNext
Loop

I started with the record showing :OLD (04/01 RUDOLPH, DON)

and ended up with OLD 03-07 KEIM, PATTY)

Got me over my problem........ and works pretty fast..

I like what you have provided - when I get a chance I will incorp into my app - much more elegant than what I came up with!

Thanks for the reply!

Dan