Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    Tim:

    Do you get an error message, or is there just the sound of crickets chirping and no change to the underlying data? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    Tim Bankerd
    Guest

    Re: Trim isn't working! (Access 97)

    Hey Shane!
    No sounds, no errors and no change in the data. I'm wondering if the special caracters in a couple of the field names may be causing a problem, (I'm guessing). Do you see any proplem with my snytax?

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    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)
    Francois

  4. #4
    Tim Bankerd
    Guest

    Trim isn't working! (Access 97)

    I've imported a Tab Delimited text file into Access 97. All of the fields are imported as Text. Each field has leading and trailing spaces that I want to get rid of. I have created an Update Query with the below SQL, but it doesn't change anything. I'm not sure why this
    isn't working.

    ----Start SQL----

    UPDATE [Tim's Transactions] SET [Tim's Transactions].[Order#] = Trim
    ([Order#]), [Tim's Transactions].[Order Date] = Trim([Order Date]),
    [Tim's Transactions].[Transaction Date] = Trim([Transaction Date]),
    [Tim's Transactions].[Transaction Time] = Trim([Transaction Time]),
    [Tim's Transactions].Transaction = Trim([Transaction]), [Tim's
    Transactions].Symbol = Trim([Symbol]), [Tim's Transactions].Quantity
    = Trim([Quantity]), [Tim's Transactions].[Order Price] = Trim([Order
    Price]), [Tim's Transactions].[Price Per/Share] = Trim([Price
    Per/Share]), [Tim's Transactions].Commission = Trim([Commission]);

    ----End SQL----

  5. #5
    Tim Bankerd
    Guest

    Re: Trim isn't working! (Access 97)

    Yes, I did get the update message. Everything looks right, but nothing is changed in the table.

    I've even removed all of the special caracters from the table and the query. Still it does nothing.

    Really Weird!

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    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! <img src=/S/beep.gif border=0 alt=beep width=15 height=15> 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?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    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.

    <pre>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
    </pre>

    Francois

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    Ok, I was faster, but your function is more flexible.
    Francois

  9. #9
    thedao
    Guest

    Re: Trim isn't working! (Access 97)

    Perhaps not as elegant, yet equally as effective: Copy one of the "phantom spaces" and paste it in a find/replace window (replace with nothing) and replace all instances in your TimsTransactions table. Any actual spaces should be preserved after the operation. Good luck.

    Thedao

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    Francois, you beat me to it! And here I thought I was all clever! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    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)

    <pre>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
    </pre>


    Edited to eliminate horizontal scrolling--Charlotte
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  11. #11
    Tim Bankerd
    Guest

    Re: Trim isn't working! (Access 97)

    Thanks to all of you!
    The solutions are great!! I've never seen a space represented as a value of 160. If I ever figure out how and why the 160 is put there, I'll let you all know.

    You're about to enter the Twilight Zone, MS Access.

    Thanks again,

    Tim Bankerd

  12. #12
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trim isn't working! (Access 97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •