Access question about the Trim function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a new user to Access and have been trying to use the "Trim" function -
but my results are not different then when I do not use it. My understanding
of trim, is that it takes out any extra spaces you may have in your text. But
when I use the function everything remains the same. Can someone please help
me with this. Thanks
 
Trim will remove leading and trailing spaces, not spaces in the middle of
the text. Also, Access will remove trailing spaces in a field when it
updates the control bound to that field.

If you want to remove spaces in the middle of a string, try the Replace
function stead.

Replace("My String", " ", "")
 
I am a new user to Access and have been trying to use the "Trim" function -
but my results are not different then when I do not use it. My understanding
of trim, is that it takes out any extra spaces you may have in your text. But
when I use the function everything remains the same. Can someone please help
me with this. Thanks
Click to expand...

Trim removes Preceeding and Trailing spaces, not spaces within a
string, so
=Trim(" This is a long text string "
will return
"This is a long text string" with the outer spaces removed, but the
inner spaces left alone.
 
Wayne said:
Trim will remove leading and trailing spaces, not spaces in the
middle of the text. Also, Access will remove trailing spaces in a
field when it updates the control bound to that field.

If you want to remove spaces in the middle of a string, try the
Replace function stead.

Replace("My String", " ", "")
Click to expand...

The Pick Trim function removed leading and trailing spaces and reduced all
internal spacing to one space. Trim("This would end up
like This.") =
"This would end up like this."

If you wanted to leave them the Rtrim and Ltrim was available.

Replace ("mystring". " ", " ")
in a loop will work if you test for double spaces.

MSFT should really do this since it makes the split function more valuable.

Pick Basic is about the same age as MSFT Basic but MSFT still has not caught
up with Pick's powerful string handling.
Outside of languages designed to handle strings there is little that can
touch it.
 
The trim function only removes leading, and trailing spaces.

From the debugger, you can type:

? trim(" a b c ")

You get:
<a b c>

However, if you have:

? trim(" a b c ")
You get:
<a b c>

You can however "roll" your own function to remove occurances of more then
one space in a row. The following function will only return vlaues

? alltrim(" a b c ")
You get:
<a b c>

The code for all tirm is:

Public Function AllTrim(v As Variant) As Variant

Dim i As Integer
Dim vbuf As Variant
Dim result As String

If IsNull(v) Then
Exit Function
End If

vbuf = Split(v, " ")

For i = 0 To UBound(vbuf)
If vbuf(i) <> "" Then
result = result & " " & vbuf(i)
End If
Next i

AllTrim = Mid(result, 2)

End Function
 
Yes, having been a long time pick/mv person, I did like the way the trim
function did work....

The other suggestion in this thread to use the "replace" function is good if
you want to remove all blanks..but removing 2, or 3 or 5 in a row, and
keeping only ONE blank is what pick did..and is a nice touch...

See my other post..as it is not much code to write your own "all trim"
function in ms-access (and, that function can be used in reports, forms, and
even sql if you want, so at least we got a extensible language!...but pick
sure got it "right" many times..and that was so long ago!!
 
Back
Top