Remove Non Printing Characters at end of text field

[raygunc]

Hi,

I have a field containing text. Some of the records have 'cr' characters (asc 10) at the end, others dont.

I want to remove them. As a start I've written a function to identify the affected records which works to a degree as follows:

Public Function stripCharacters(strAddress As String)
Dim strLastChar As String
Dim lngAscLastChar As Long
lngAscLastChar = Asc(strLastChar)

If lngAscLastChar = 10 Then
    stripCharacters = "CR"
Else
    stripCharacters = "None"
End If
End Function

This has been placed as a column in a query and succesfully identifies those with the offending character and those that don't. However when I tried to filter by entering "CR" in the criteria of this calculated column Access produces an error - "Run Time Error 5 - Invalid Procedure Call or Argument".

As not all the records need amending I need to identify them first, If I can't do this I'm stuck

Where am I going wrong, and is there an easy solution to my problem.

 

[earthandfire]

Try

Public Function stripCharacters(strAddress As String) As String

If Right(strAddress,1) = chr(10) Then
    stripCharacters = Left(strAddress, Len(strAddress) -1
Else
    stripCharacters = strAddress
End If
End Function

NB This checks ONLY for <CR> as the last character, nothing else

 

[PHV]

You don't need an UDF at all:
IIf([Address field] Like '*' & Chr(10), Left([Address field],Len([Address field])-1), [Address field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[raygunc]

Thank you both for your help.

I used PHV's solution. It works a treat.

thanks again for your help

Ray