Custom find and replace to alter text in a field

[secondangel]

Hiya tried seraching but no avail.

Im scrolling through a recordset and as its from data imported sometimes i get characters in some of the fields that i dont want. As they are in diff places i need a find and replace type function to remove these characters.

ANy help or ideas much appreciated

Code ive got is

  prodcost = 0
  prodsell = 0
  
  
  Dim art As String
  Dim tit As String
  
  
  Set rst = db.OpenRecordset("tblTempImport")
  
  If rst.BOF And rst.EOF Then
    MsgBox "table is empty", vbCritical
  Else
    rst.MoveFirst
    Do Until rst.EOF
    
    rst.Edit
    rst.Fields(0) = strImpName
    rst.Update
    
    prodcost = 0
    prodsell = 0
    
    prodcost = CInt(rst.Fields(5))
    prodsell = Round(prodcost * 1.68, 0)
    
    rst.Edit
    rst.Fields(6) = CStr(prodsell)
    rst.Update
    
    
    ' read the string value from artist and title into a temp variable
    ' change it by removing the characters
    ' write it back to the fields

    art = rst.Fields(2)
    tit = rst.Fields(3)
    
    
   [B] ' what goes here ?????????[/B]
    
    
    rst.MoveNext
  Loop
  End If
  MsgBox "all new records updated", vbInformation

all the rest works byt the way, ive just cut it out of the big chunk of code - so all the other varibales are delcared etc etc. its the what goes here bit

Many thanks

Ashley

 

[secondangel]

im presuming soemthing like calculating th length. then reading each letter to see if it is what i want and then if not skipping it, loop, and only copy each letter across i want to the new value.

 

[sportsguy]

Function ReplaceWord(anyString As String, whatWord As String, withWhat As String) As String

'ReplaceWord([CustomerName],".","")   Removes a period


Dim anyLength As Byte
Dim WhatLength As Byte
Dim I As Byte

anyLength = Len(anyString)
WhatLength = Len(whatWord)

For I = 1 To anyLength
    If Mid(anyString, I, WhatLength) = whatWord Then
        anyString = Mid(anyString, 1, I - 1) & withWhat & Mid(anyString, I + WhatLength, anyLength)
        anyLength = anyLength - WhatLength
    End If

Next I

ReplaceWord = anyString

End Function

only because I like your name. . .
ashley


regards

sportsguy