[AccessD] Replace multiple spaces with a single space

Why not a straight forward replace function called recursively, viz:

Option Compare Database
Option Explicit
Private str As String
Function xyz()
  str = "This          string             has     too       many
spaces"
  Call nospaces
End Function


Function nospaces() As String
str = Replace(str, "  ", " ")
Debug.Print str
If InStr(str, "  ") > 0 Then
    Call nospaces
End If
End Function


Max

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: 16 November 2009 10:12
To: Access Developers discussion and problem solving
Subject: [AccessD] Replace multiple spaces with a single space

An rather neat expression to get rid of extra spaces in strings
i.e.   Convert "This          string             has     too       many
spaces" to
"This string has to many spaces" 

Based on an TSQL set based method in the latest SQLServerCentral newsletter.
Can either be used directly as an expression in a  query or you could create
a single line 
function for it (inline in a query should be more efficient)

'uses chr$(7) as a safe substitution character which shouldn't appear in the
original
'1. replaces "space + space" with  "space + Chr$(7)"
'2. replaces "chr$(7) + space" with an empty strings
'3. replaces left over chr$(7)s with empty strings

ReduceSpaces = Replace(Replace(Replace(Trim(OriginalString), "  ", " " _
               + Chr$(7)), Chr$(7) + " ", ""), Chr$(7), "")

Can be used almost unchanged in a TSQL stored procedure - just replace
Chr$() with 
CHAR() and Trim() with LTRIM(RTRIM())  


-- 
Stuart
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com