[AccessD] Trim in Access 2007

Michael

I think if you change the constant to 

  Const cstrSpace As String * 2 = "  " 

It should replace all double spaces with a single space.  If there are
multiple spaces, especially if the field is right packed, the loop would
have to be rerun until no double spaces exist.


Perry Harold


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, November 30, 2007 10:00 AM
To: accessd at databaseadvisors.com
Subject: Re: [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


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