Trim not working? (1 Viewer)

[AccessKurzo]

I have a column in one table where all the records have either 2 or 3 spaces that trail. I try using Trim on it, but it doesn't seem to have an effect.

Trim works in other areas of my application, but on these records, something seems odd. Is it possible that these are special 'placeholders' that Trim cannot remove? If so, how can I remove these trailing spaces?

Thanks

 

[WayneRyan]

AccessKurzo,

If you are familiar with code, you can use the OnClick event
of one of your textboxes to put:

Stop

When you click on the field your "code" will stop at stop.
You can do: View --> Immediate Window

In the immediate window you can:

? ASC(Mid(Me.YourTextBox, Len(Me.YourTextBox), 1))

It will show you the ASCII value of your last character.

You can then do

? ASC(Mid(Me.YourTextBox, Len(Me.YourTextBox) - 1, 1))

For the second to last (and so on).

Then remove the stop and let me know what you found.

Wayne

 

[AccessKurzo]

thanks for the reply -

it returns a value of 160

I guess I can just keep checking the string with this and cut off whatever ending chars = 160.

Does that make sence?

 

[WayneRyan]

AccessKurzo,

Just a quick thought.

I take it that this data was inherited. If it is localized
to one field it is possible to:

Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Dim Temp As String
Dim Ptr As Integer

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)
While Not rst.EOF and Not rst.BOF
   Temp = ""
   For Ptr = 1 to Len(rst!YourField)
      If Asc(Mid(YourField, Ptr, 1)) < 127 Then
         Temp = Temp & Mid(YourField, Ptr, 1)
      End If
      Next Ptr
      rst.Edit
      rst!YourField = Temp
      rst.Update
      rst.MoveNext
   Wend

Wayne

 

[AccessKurzo]

Wayne,

this is what I did -
With rs
Do While Not .EOF
lngID = rs(0)
strName = rs(1)
If Asc(Mid(strName, Len(strName) - 2, 1)) = 160 Then
strName = Left(strName, Len(strName) - 3)

ElseIf Asc(Mid(strName, Len(strName) - 1, 1)) = 160 Then
strName = Left(strName, Len(strName) - 2)

ElseIf Asc(Mid(strName, Len(strName), 1)) = 160 Then
strName = Left(strName, Len(strName) - 1)
End If

DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblPricing SET tblPricing.ProductDesc = '" & strName & "' WHERE (((tblPricing.PriceID)=" & lngID & "));")

rs.MoveNext
Loop

it works good enough for what I need

Thanks alot for your help!

Curtis

 

[WayneRyan]

Curtis,

Out of curiosity, where did the data originate from?

Wayne