I looked at your query and was confused also. I believe that you have a non printed ASCII character in the first position and Trim sees it as real character. In your case, I put ASC(Std_ID) in a new column and it returned 160 which is idnetified as a non-breaking space (no symbol)
To delete these non printed characters you will have to use a combination of the Len function and the Right function
Something like =Right(Std_ID,Len(Std_ID)-2) as I believe that there are two spaces in the field.
thans a lot
i have done this in excel and able to exclude non printing characters but i have no knowledge wchich can be used to know this kind of spaces,
with the help of ASC i am able to what i want to do
I was also running into the same problem when pulling extracts from Excel to MS Access and did find that a leading ASCII value 160 was the problem (as previously posted).
Seems silly that the Trim function doesn't get rid of all leading and trailing spaces. I don't know why I'd ever want to use Trim and not have it get rid of the leading space, even if it was a "Non-breaking space".
That said, I made a little function to use in an update query to get rid of the any spaces (i.e., either ascii 32 or 160). It works well and this issue comes up now and again with me...
Code:
Public Function RemoveSpaces(ByVal strField As String) As String
If InStr(strField, Chr(160)) > 0 Then
RemoveSpaces = Replace(strField, Chr(160), "")
ElseIf InStr(strField, Chr(32)) > 0 Then
RemoveSpaces = Replace(strField, Chr(32), "")
Else
RemoveSpaces = strField
End If
End Function
Once you have this, just throw that into an Update query and it will get rid of any leading spaces. Well actually it will get rid of all spaces... so just be aware of that, if your string is uspposed to have spaces... you might want to take out this part of the function:
Which I only put in because sometimes, when looking at the data, I have deleted the hard return space (i.e., Chr(160)) and replaced it with a space bar space (i.e., Chr(32)). So I figured I'd remove any spaces, since my strings do not have spaces.
I know this is an old post but I recently came across the same issue and using gblacks code as a base used the following two line solution which trims the text and then the replace removes the Non-breaking spaces.
Code:
Public Function True_Trim(strText As String) As String
On Error GoTo Error_Handler
strText = Trim(strText)
strText = Replace(strText, Chr(160), "")
Exit_Function:
On Error Resume Next
True_Trim = strText
Exit Function
Error_Handler:
msgbox Err.Description
strText = ""
Resume Exit_Function
End Function
Well if the hard space is in the front or back of the string, it would not get trimmed?
However on examining your "" a little closer, it seems there is not a space between each character. I initially thought there was.
Also what happens if it happens to be Chr(32)?
Well if the hard space is in the front or back of the string, it would not get trimmed?
However on examining your "" a little closer, it seems there is not a space between each character. I initially thought there was.
Also what happens if it happens to be Chr(32)?
The post was about trimming a string so I was only dealing with regular and non-breaking spaces at the beginning or end of a string, but the replace would take care of the non-breaking spaces where ever they appeared in the string, if you wanted to get rid of all spaces in the string then its just two replace statements.
A small addition to the content:
With the Replace from the regular expressions you can bundle several values to be removed in one statement. You could also make sure that these values are only removed at the beginning or end of the actual expression, i.e. correspond to the meaning of Trim. Removing spaces in the middle of the printout may well be undesirable.
In 30 years, I've never found a use for a non-breaking space whether at the start, in the middle or at the end of a string, so for my use I'm happy to remove them wherever they appear. Others should amend the code to suit their needs.