MS Access SQL: use update and replace to remove multiple spaces with a single one

Question

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.

Answer 1

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+',' ')