If I have a field in a table in Access......

[cchen]

The field has a bunch of text in it. Is there any way to remove all the excess white spaces?

It'll look something like this: asdf (space) (space) (space) (space) asdf
when i want it to look like this: asdf adf asdf etc

any way to do this besides doing it manually?

 

[Legacy 16232]

Use the Edit -> Replace utility. You can replace a single space with nothing to remove all of them, or you can replace two spaces with one (repeatedly) to remove duplicates.

 

[mdmilner]

Yes, possibly, but my first thought seems messy and requires code.
You'd have to start with parsing the field character by character.

How about this:

1) Read field value into variable
2) LTrim (trim all spaces to left)
3) InStr to first space. Subtract one. Extract the characters up to and including that position into a new field adding a single space afterwards
4) Repeat step 1/2 until done
5) .Edit/.Update the new value into table

So, did this real quickly so it might not be the easiest way to accomplish it.

Public Function reParse()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strAString As String
Dim strW As String
Dim x As Integer

Set dbs = CurrentDb()

Set rs = dbs.OpenRecordset("Select * from tblparse", dbopendynaset)

With rs
  Do Until rs.EOF
    strAString = LTrim(.Fields(0).Value)
    strW = ""
    Do Until Len(strAString) = 0
      x = InStr(strAString, " ")
      If x = 0 Then Exit Do
      strW = strW & Left(strAString, x - 1) & " "
      strAString = Mid(strAString, x + 1, Len(strAString) - x + 1)
      strAString = LTrim(strAString)

      .Edit
      .fields(0).Value = strW
      .Update

    Loop
    .MoveNext
  Loop
End With

Set rs = Nothing
Set dbs = Nothing
End Function

Oh, you can use the fieldname instead of .fields(0).Value

Mike