[AccessD] Trim in Access 2007

Hi Matt

Trim only trim outer spaces. Use MTrim to trim inner spaces as well:

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 As String * 1 = " "
  
  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

/gustav

>>> mmattys at rochester.rr.com 30-11-2007 15:46 >>>
Hi Gustav,

I am doing the common operation of trimming fields that have
extra spaces in them. These probably came from a fixed-width
text-file, but I only receive the accdb. 

I execute the query and it has no effect on the spaces.

Michael R. Mattys
MapPoint & Access Dev
www.mattysconsulting.com 

----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, November 30, 2007 9:31 AM
Subject: Re: [AccessD] Trim in Access 2007


> Hi Michael
> 
> Haven't followed this thread until now. 
> Could you explain what you try to do? Clear as mud for me ...
> 
> /gustav