UPDATE tblMyTable SET myField = Trim(myField)
....will trim leading and trailing spaces, however, if you want to remove
spaces from the middle, the following will work on Access 2002 and later:
UPDATE tblMyTable SET myField = Replace(myField, " ", "")
....however, in Access 2000 and prior, you'll need to create your own Replace
function (shown below), and call it thus:
UPDATE tblMyTable SET myField = Repl(myField, " ", "")
Public Function Repl(sExpression As String, _
sFind As String, sReplace As String) As String
Dim iPos As Integer
If Len(sExpression) > 0 And Len(sFind) > 0 Then
'Check to see if sFind exists
iPos = InStr(1, sExpression, sFind)
Do While iPos > 0
'It exists - replace it
Repl = Left(sExpression, iPos-1) & sReplace & _
Mid(sExpression, iPos + Len(sFind))
'As long as we're not at the end of sExpression,
'check to see if there is room for more instances
If iPos < Len(sExpression) Then
'Yep, there's room - check for sFind again
iPos = InStr(iPos + 1, sExpression, sFind)
Else
'Nope, there's no more room, so there can't be
'any more instances
iPos = 0
End If
Loop
Else
'There are no instances - set the return value = sExpression
Repl = sExpression
End If
End Function
See
http://www.pacificdb.com.au/MVP/Code/repl.htm
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
