[AccessD] Trim in Access 2007

Thanks Gustav,

That took out all spaces, proving the table is not corrupt
However, these are addresses ... :)

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 10:00 AM
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