[AccessD] Remove spaces from field

Great!
Thanks to both Jim and Gustav for your responses
I used the Replace() function and got exactly what I needed.

Thanks again,
Mark

-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Thursday, July 31, 2003 1:52 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Remove spaces from field

Hi Mark

> How can I remove any spaces from data in a field?

> For example, Field1 contains 'johnsmith   01/01/1995 ABC'.  I want the
> field to be 'johnsmith01/01/1995ABC'.

If you don't have the Replace() function handy you can use this which
is especially useful for very long strings:

<code>

Public Function MTrim (ByVal strString As String) As String

' Trims strString for mid and outer spaces.
'
' 1999-06-23. Cactus Data ApS. CPH.

  Const cstrSpace = " "
  
  Dim lngTemp As Long
  Dim lngChop As Long
  Dim lngLoop As Long
  Dim strTemp As String
  Dim strTrim As String
    
  strTemp = Trim(strString)
  lngTemp = Len(strTemp)
  If lngTemp > 0 Then
    strTrim = strTemp
    lngChop = 1
    Do
      lngChop = InStr(lngChop, strTrim, cstrSpace)
      If lngChop > 0 Then
        ' A space is found. Shift one character and
        ' overwrite this space in string strTrim.
        lngLoop = lngLoop + 1
        Mid(strTrim, lngChop) = Mid(strTemp, lngChop + lngLoop)
      End If
    Loop Until lngChop = 0
    ' String strTrim now contains no spaces.
  End If
  
  ' Return net length of trimmed string.
  MTrim = Left(strTrim, lngTemp - lngLoop)

End Function

</code>

/gustav

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