sql - Access VBA to trim space around a specific character or words(s) in a column

Question

I have this piece of code that originally uses the replace function(VBA) to replace certain characters in a string or column field in MS Access. The code is in a module.

I need to modify the code so that it trims the space around certain characters. Example, if th appears alone, trim the space from the left, so that it flushes against the numbers. If it has more than one tab(space) to the right, trim it so that there is only one tab space to the right. Same for avenue, trim it so that one tab space is on the left and right.

Here is what I have written:

Public Function TrmSpace(RemoveSpace As String) As String
Dim UserEntry As Variant, CorrectedEntry As Variant
Dim i As Long
ExpectedEntry = Array("th ", " th", " th ", "TH")

CorrectedEntry = Array("th", "th", "th", "th")
TrmSpace = RemoveSpace
For i = 0 To 3
    TrmSpace = Trim(TrmSpace, ExpectedEntry(i), CorrectedEntry(i), Compare:=vbTextCompare)
Next
End Function

I changed the function from Replace to Trim but I am doing it wrong.

Thanks everyone!

Guy

Answer 1

Just to add to Jesse's answer. Instead of doing so many replaces you can simply do the following (with a reference to Excel):

Public Function TrmSpace(RemoveSpace As String) As String

  RemoveSpace = Excel.Application.WorksheetFunction.Trim(RemoveSpace)
  RemoveSpace = Replace(RemoveSpace," th","th",,,vbTextCompare)

  TrmSpace = RemoveSpace

End Function

The excel function Trim is different from the vbaTrim. The excel Trim removes all spaces that are at the beginning and end and also any double/triple/etc spaces in the string. Using vbTextCompare in your replace function will make it so it doesn't matter what mix of "th" is, so you don't need to worry if it is "TH" or "tH" or "Th", etc.