Trim isn’t working! (Access 97)

Topic

New Reply

Tim:

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

Reply | Quote

Replies

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

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! 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

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

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

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

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

Reply | Quote

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