MS Access - Search all fields for a character and replace it with another

I have a VBA module that exports a number of tables in a CSV format. The end user now needs these files to be in a pipe deliminted format "|" or ASCII. I have the following code which works fine:

Dim vbpipe As String
vbpipe = Chr(124)
Debug.Print vbpipe

I can then use vbpipe to delimit the output. Prior to doing that, I would like to search all fields in the table for the "|" character and replace any existing pipes with a "~" tilde, so that my outputs will be clean. What I am trying to avoid is specifying each field in each table and searching and replacing individually.

I found the code below on the web and it is close, but not exactly what I want. I don't want to do an entire database, and I only want to replace one character, not two. I do not understand how to use arrays properly, so I did not want to try to play with this code, but it might be a helpful starting point.



Private Sub fixCharacters()
Dim rst As Recordset, fld As Field
Dim varCharsIn As Variant, varCharsOut As Variant
Dim strSplit() As String
Dim i As Integer
Rem set up an array of characters to be replaced
varCharsIn = Array("è", "§")
Rem set up array of replacement characters - same relative positions as above array
varCharsOut = Array("c", "z")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [table];")
rst.MoveFirst
Do Until rst.EOF
  For Each fld In rst.Fields
    Select Case fld.Type
    Rem do this for text fields only
    Case dbText
         Rem ensure field is not null
         If Nz(fld, vbNullString) <> vbNullString Then
           Rem step through the input array to check for matches in field
           For i = 0 To UBound(varCharsIn)
             Rem split on the target character
             strSplit = Split(fld, varCharsIn(i))
             Rem if the split array has only one dimension,there's no match
             If UBound(strSplit) > 0 Then
               Rem found a match
               rst.Edit
               Rem rejoin the array using the corresponding replacement character
               rst.Fields(fld.Name) = Join(strSplit, varCharsOut(i))
               rst.Update
             End If
           Next
         End If
    Case Else
    End Select
  Next
  rst.MoveNext
Loop
End Sub
Rick RudolphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this code

Sub fixCharacters()
Dim rst As Recordset, fld As Field, strTable As String
strTable = "TableName"    '<< CHANGE WITH ACTUAL NAME OF TABLE
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [" & strTable & "];")
  For Each fld In rst.Fields
    Select Case fld.Type
    Case dbText
        CurrentDb.Execute "update [" & strTable & " ] set  [" & fld.Name & "] = replace([" & fld.Name & "],'|','~')"
    Case Else
    End Select
Next
rst.close
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick RudolphAuthor Commented:
Very Clean, works perfectly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.