• Trim isn’t working! (Access 97)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Trim isn’t working! (Access 97)

    Author
    Topic
    WSShane Sargent
    AskWoody Lounger
    August 30, 2001 at 3:26 pm #359701

    Tim:

    Do you get an error message, or is there just the sound of crickets chirping and no change to the underlying data? grin

    Reply | Quote
    Viewing 3 reply threads
    Author
    Replies
    • WSFrancois
      AskWoody Lounger
      August 30, 2001 at 3:49 pm #539891

      Did you receive the to message :
      You are about to run an update query that will modify data in your table.
      You are about to update xxx row(s)

      Reply | Quote
    • WSShane Sargent
      AskWoody Lounger
      August 30, 2001 at 6:34 pm #539923

      Huh, that’s some funky stuff. It wouldn’t Trim, and I didn’t see anything in the properties of the field that would prohibit trimming extraneous spaces. (I used just the Transaction field as a test bed)

      I used the InStr function to look for a space character, and it reported back that there were no spaces! beep I’m going on a hunt for a function that will step through a string and report back what each character is; that is, I see a space, but what does Access see?

      Reply | Quote
    • WSFrancois
      AskWoody Lounger
      August 30, 2001 at 7:36 pm #539936

      Tim,
      A space has as ascii value of 32.
      Trim is used to remove spaces as they have an ascii value of 32
      The strange thing is that the space in your tables, have a value of 160. Why ???? I don’t know.
      Here a little function to replace all the space with ascii value of 160 by a space with ascii value of 32.
      Put that function in a module and save it.
      In your query replace all Trim([YourField]) by Trim(Strip160([YourField]) and run the query
      This should work.

      Function Strip160(strToStrip As String) As String
Dim x As Integer
For x = 1 To Len(strToStrip)
   If Asc(Mid(strToStrip, x, 1))  160 Then
      Strip160 = Strip160 & Mid(strToStrip, x, 1)
   Else
      Strip160 = Strip160 & " "
   End If
Next x

End Function
      Reply | Quote
      • WSShane Sargent
        AskWoody Lounger
        August 30, 2001 at 9:32 pm #539940

        Francois, you beat me to it! And here I thought I was all clever! shrug

        Tim, sure enough, it’s a space, but not a space. Very Zen. Francois’ solution will certainly do the trick for you. I made use of the following function by replacing the Trim([FieldName]) from your original SQL with ChangeStr([FieldName],Chr(160),””,1)

        Public Function ChangeStr(strOrig As String, strOldChar As String,  _
                    strNewChar As String, intMatchCase As Integer) As Variant
'This substitutes one character or char string with another as
'designated in the calling function, from KB Q210372
'intMatchCase = 0 means case insensitive comparison; set to 1 for case
'sensitive comparison against strOldChar
Dim Temp As String
Dim Pos As Integer

Temp = ""

If IsNull(strOrig) Then
    ChangeStr = Null
    Exit Function
End If

If strOldChar = "" Or strOrig = "" Then
    ChangeStr = strOrig
    Exit Function
End If

Pos = InStr(1, strOrig, strOldChar, intMatchCase)
While Pos > 0
    Temp = Temp & Mid$(strOrig, 1, Pos - 1) & strNewChar
    strOrig = Right$(strOrig, Len(strOrig) - Pos - Len(strOldChar) + 1)
    Pos = InStr(1, strOrig, strOldChar, intMatchCase)
Wend
ChangeStr = Temp & strOrig

End Function

        Edited to eliminate horizontal scrolling–Charlotte

        Reply | Quote
    • WSTomG
      AskWoody Lounger
      September 2, 2001 at 5:32 pm #540292

      Sounds like you want to check out a solution on the side of the program you used to export the data from, so that when Access imports, it doesn’t have to deal with extended ASCII codes. Access is not the twilight zone by any means, and there are some really good tomes on the subject.

      Reply | Quote
    Viewing 3 reply threads
    Reply To: Trim isn’t working! (Access 97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.