Fordraiders
asked on
Trim function not working
access 2003
This is not deleting trailing spaces ?
UPDATE tblData SET tblData.fldMfrnum = Trim([fldMfrnum]);
Thanks
fordraiders
This is not deleting trailing spaces ?
UPDATE tblData SET tblData.fldMfrnum = Trim([fldMfrnum]);
Thanks
fordraiders
Are you sure you don't have carriage returns in fldMfrnum? They can often look like trailing spaces until you expand the height of the rows when viewing the data in the table.
ASKER
ok...that may be it ?
how do i get rid of them.. please
how do i get rid of them.. please
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you tell which character it is from this ?
fldMfrnum
22119
22251
23028
23116
23484
23485
23701
24065
24577
24581
24583
24585
24683
24829
25024
ASKER
chr 13 and 10 not doing it... ?
Thanks
Thanks
Is the datatype on the field numeric or text?
you will need replace function
replace([fldMfrnum],"  ;","")
or
just use
trim(val([fldMfrnum]))
replace([fldMfrnum]," 
or
just use
trim(val([fldMfrnum]))
ASKER
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(160),"");
It was a non breaking space...?
It was a non breaking space...?
did you try
UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
Going back to the beginning, what was the problem? You have a string with trailing blanks you want to get rid of. Most things suggested here will do that. Then what?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gray l,
Going back to the beginning, what was the problem?
I have a field with data.
at the end of the last character there are what appear to be carriage returns or spaces...
1 or 2 sometimes 3
You have a string with trailing blanks you want to get rid of. Most things suggested here will do that.
Then what?
I was testing out different chr(<character>) chr(160) seemed to get rid of the spaces..?
I have never come acrosss this chr before..
cactus data suggestion did not work either...
Without suggesing a explicit chr(160)..
Capricorn1 suggestion worked fine...
UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
But I would like to know why it catches chr(160) ??
Thanks for ALL the comments
Going back to the beginning, what was the problem?
I have a field with data.
at the end of the last character there are what appear to be carriage returns or spaces...
1 or 2 sometimes 3
You have a string with trailing blanks you want to get rid of. Most things suggested here will do that.
Then what?
I was testing out different chr(<character>) chr(160) seemed to get rid of the spaces..?
I have never come acrosss this chr before..
cactus data suggestion did not work either...
Without suggesing a explicit chr(160)..
Capricorn1 suggestion worked fine...
UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
But I would like to know why it catches chr(160) ??
Thanks for ALL the comments
Capricorn1 suggestion worked fine...
UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
<But I would like to know why it catches chr(160) ??>
trim(val([fldMfrnum]))
val([fldMfrnum]) ->> retrieves all numeric characters starting from position 1 and stops at the position of the first non numeric character
so using val in a string like
val(12345Xdj 4578 amwhatever character ) returns 12345
val(A123456) returns 0
UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
<But I would like to know why it catches chr(160) ??>
trim(val([fldMfrnum]))
val([fldMfrnum]) ->> retrieves all numeric characters starting from position 1 and stops at the position of the first non numeric character
so using val in a string like
val(12345Xdj 4578 amwhatever character ) returns 12345
val(A123456) returns 0
ASKER
capricorn, one small glitch...
It if the entry is a alphanumric or alpha ABC123A OR BUSSMANN
IT SETS THE ENTRY TO "0"
It if the entry is a alphanumric or alpha ABC123A OR BUSSMANN
IT SETS THE ENTRY TO "0"
i just posted it above..
well, you have to consider all the the data formats of a field before you can use a working codes.
my codes was based on the sample data you posted
well, you have to consider all the the data formats of a field before you can use a working codes.
my codes was based on the sample data you posted
what do you want to do with this type of data
{ ABC123A OR BUSSMANN }
{ ABC123A OR BUSSMANN }
if you want to remove special characters from a field of mixed type of data,
you will need a function to do that
something like this
Function CleanString(sString As String) As String
Dim sReturn As String, i
sReturn = ""
For i = 1 To Len(sString)
If (Asc(Mid(sString, i, 1)) >= 32 And Asc(Mid(sString, i, 1)) <= 127) Then
sReturn = sReturn & Chr(Asc(Mid(sString, i, 1)))
End If
Next i
CleanString = Trim(sReturn)
End Function
you will need a function to do that
something like this
Function CleanString(sString As String) As String
Dim sReturn As String, i
sReturn = ""
For i = 1 To Len(sString)
If (Asc(Mid(sString, i, 1)) >= 32 And Asc(Mid(sString, i, 1)) <= 127) Then
sReturn = sReturn & Chr(Asc(Mid(sString, i, 1)))
End If
Next i
CleanString = Trim(sReturn)
End Function
ASKER
the same thing,
but if the spaces(or carriage returns or whatever(spaces)) exist after the last character or digit...it gets rid of them..
The data format in this field is "text". It will always be "Text".
Yes, the data I showed just happend to come that way to me..
Sorry for the confusion to all !!
Thanks Cap...
but if the spaces(or carriage returns or whatever(spaces)) exist after the last character or digit...it gets rid of them..
The data format in this field is "text". It will always be "Text".
Yes, the data I showed just happend to come that way to me..
Sorry for the confusion to all !!
Thanks Cap...
Then fall back to Replace:
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Asc(160), "");
or:
UPDATE tblData SET tblData.fldMfrnum = Trim(Replace([fldMfrnum], Asc(160), ""));
/gustav
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Asc(160), "");
or:
UPDATE tblData SET tblData.fldMfrnum = Trim(Replace([fldMfrnum], Asc(160), ""));
/gustav
Sorry, Chr():
UPDATE tblData SET tblData.fldMfrnum = Trim(Replace([fldMfrnum],C hr(160),"" ));
/gustav
UPDATE tblData SET tblData.fldMfrnum = Trim(Replace([fldMfrnum],C
/gustav
ASKER
cap, On your function what about capturing the chr(160) ?
that function eliminates all that is not within the scope of chr(32) - chr(127)
what do you mean by
<what about capturing the chr(160) ?>
if instr(fieldname,chr(160))> 0 then
'what do you want to do with chr(160)?
end if
what do you mean by
<what about capturing the chr(160) ?>
if instr(fieldname,chr(160))>
'what do you want to do with chr(160)?
end if
ASKER
cap,
if instr(fieldname,chr(160))> 0 then
'what do you want to do with chr(160)?
get rid of the space just like the others..
if instr(fieldname,chr(160))>
'what do you want to do with chr(160)?
get rid of the space just like the others..
did you try the function i posted above?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Utilizing the function ...thanks to all....
better use this function to get values 0-9 A-z
Function CleanString(sString As String) As String
Dim sReturn As String, i
sReturn = ""
For i = 1 To Len(sString)
If (Asc(Mid(sString, i, 1)) >= 48 And Asc(Mid(sString, i, 1)) <= 57) _
Or (Asc(Mid(sString, i, 1)) >= 65 And Asc(Mid(sString, i, 1)) <= 90) _
Or (Asc(Mid(sString, i, 1)) >= 97 And Asc(Mid(sString, i, 1)) <= 122) Then
sReturn = sReturn & Chr(Asc(Mid(sString, i, 1)))
Else
'Debug.Print Asc(Mid(sString, i, 1))
End If
Next i
CleanString = Trim(sReturn)
End Function
Function CleanString(sString As String) As String
Dim sReturn As String, i
sReturn = ""
For i = 1 To Len(sString)
If (Asc(Mid(sString, i, 1)) >= 48 And Asc(Mid(sString, i, 1)) <= 57) _
Or (Asc(Mid(sString, i, 1)) >= 65 And Asc(Mid(sString, i, 1)) <= 90) _
Or (Asc(Mid(sString, i, 1)) >= 97 And Asc(Mid(sString, i, 1)) <= 122) Then
sReturn = sReturn & Chr(Asc(Mid(sString, i, 1)))
Else
'Debug.Print Asc(Mid(sString, i, 1))
End If
Next i
CleanString = Trim(sReturn)
End Function