Trim

[eavan]

Hi,
i am not an experienced coder and want to create a procedure that will trim column information in a specific table(and can be re-runable)in MS Access.So if there is a vb sub procedure that i must run i would greatly appreciate it.Cheers
p.s When i mean trim i mean removing all the white space at the beginning of columns and between strings.I need this for all the columns of an entire table.

 

[Norris68]

You will need to write a public function to do it. I have left the 'S' parameter as a variant so it can handle Null fields too.

e.g.
Public Function StripSpaces(S As Variant) As String
On Error Resume Next
Dim R As String
Dim A As Long
R = ""
If Not IsNull(S) And Len(S) > 0 Then
For A = 1 To Len(S)
If (Mid$(S, A, 1) <> &quot; &quot Then R = R & Mid$(S, A, 1)
Next A
End If
StripSpaces = R
End Function

Then you could call it within an UPDATE query as:

UPDATE MyTable SET MyField1 = StripSpaces(MyField1), MyField2 = StripSpaces(MyField2), etc.

 

[eavan]

Thats great.Appreciate that.i have setup the update in a runsql macro and i think that is working fine