3

In MS Access I've tried to use:

UPDATE Table SET FieldName= REPLACE(FieldName, '\s+', '\s');

to remove multiple spaces from a field, but it doesn't work.

Sefran2
  • 3,578
  • 13
  • 71
  • 106
  • What is happening? How is it not working? Start by doing the replace in a select to figure it out without harming your data. – simbabque Feb 24 '14 at 10:41
  • I tried directly on the Table (on a db backup). After the execution, the spaces are still there – Sefran2 Feb 24 '14 at 10:42
  • 4
    There is no built in regex support in Access, `replace()` uses simple strings. See http://stackoverflow.com/questions/748674/how-to-replace-multiple-characters-in-access-sql – Alex K. Feb 24 '14 at 10:43

2 Answers2

4

As mentioned in the comments to the question, the Replace() function does not support regular expressions. However, you could accomplish your goal with the following VBA code:

Option Compare Database
Option Explicit

Sub RemoveMultipleSpaces()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Do While DCount("FieldName", "TableName", "FieldName LIKE ""*  *""") > 0
        cdb.Execute "UPDATE TableName SET FieldName = Replace(FieldName,""  "","" "")"
    Loop
    Set cdb = Nothing
End Sub

edit re: comment

Alternatively, you could use the following code which uses regular expressions to find the replacement candidates:

Option Compare Database
Option Explicit

Public Function RegexReplace( _
        originalText As Variant, _
        regexPattern As String, _
        replaceText As String, _
        Optional GlobalReplace As Boolean = True) As Variant
    Dim rtn As Variant
    Dim objRegExp As Object  ' RegExp

    rtn = originalText
    If Not IsNull(rtn) Then
        Set objRegExp = CreateObject("VBScript.RegExp")
        objRegExp.Pattern = regexPattern
        objRegExp.Global = GlobalReplace
        rtn = objRegExp.Replace(originalText, replaceText)
        Set objRegExp = Nothing
    End If
    RegexReplace = rtn
End Function

Usage example:

RegexReplace("This is     a test.","\s+"," ")

returns 

This is a test.

You would use it in a query like this:

UPDATE TableName SET FieldName = RegexReplace(FieldName,'\s+',' ')
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • does your code replace more than two spaces? I don't see regular expression in the vba code. – Sefran2 Feb 24 '14 at 11:14
  • @Cricket Yes, the `While` loop keeps looking for sequences of two consecutive spaces and replaces them with a single space. It keeps doing that until all the double spaces are gone (and `DCount()` returns zero). – Gord Thompson Feb 24 '14 at 11:24
  • Is it possible to use regular expression instead? – Sefran2 Feb 24 '14 at 11:29
  • if I use RegexReplace in the Sub, I obtain that the RegexReplace isn't defined in the expression. Have I define it in any way? – Sefran2 Feb 24 '14 at 12:06
  • @Cricket Just use `RegexReplace()` in the query itself, instead of the (built-in) `Replace()` function you originally tried to use. Also note that the `RegexReplace()` function must be in a standard VBA Module, not a Class Module for a form or report. – Gord Thompson Feb 24 '14 at 12:19
  • Perfect. Than you very much. I've forgotten the Option Explicit – Sefran2 Feb 24 '14 at 12:32
1

This function remove multiple spaces and also tabs, new line symbols etc.

Public Function removeObsoleteWhiteSpace(FromString As Variant) As Variant
  If IsNull(FromString) Then 'handle Null values
    removeObsoleteWhiteSpace = Null
    Exit Function
  End If
  Dim strTemp As String
  strTemp = Replace(FromString, vbCr, " ")
  strTemp = Replace(strTemp, vbLf, " ")
  strTemp = Replace(strTemp, vbTab, " ")
  strTemp = Replace(strTemp, vbVerticalTab, " ")
  strTemp = Replace(strTemp, vbBack, " ")
  strTemp = Replace(strTemp, vbNullChar, " ")
  While InStr(strTemp, "  ") > 0
    strTemp = Replace(strTemp, "  ", " ")
  Wend
  strTemp = Trim(strTemp)
  removeObsoleteWhiteSpace = strTemp
End Function
user1032559
  • 1,577
  • 1
  • 14
  • 16