[AccessD] Trim in Access 2007

Yes! Excellent, Gustav.

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:30 AM
Subject: Re: [AccessD] Trim in Access 2007


> Hi Michael
> 
> OK, then try to modify it like this:
> 
> Const cstrSpace As String * 2 = "  "
> 
> /gustav
> 
>>>> mmattys at rochester.rr.com 30-11-2007 16:12 >>>
> 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