[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 653
  • Last Modified:

Trim function not working

access 2003

This is not deleting trailing spaces   ?

UPDATE tblData SET tblData.fldMfrnum = Trim([fldMfrnum]);

Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 10
  • 10
  • 3
  • +2
3 Solutions
 
Chris MangusDatabase AdministratorCommented:
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.
0
 
FordraidersAuthor Commented:
ok...that may be it ?

how do i get rid of them.. please
0
 
Chris MangusDatabase AdministratorCommented:
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(13),"");

...and...

UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(10),"");
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
FordraidersAuthor Commented:
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  

Open in new window

0
 
FordraidersAuthor Commented:
chr 13 and 10   not doing it... ?

Thanks
0
 
Chris MangusDatabase AdministratorCommented:
Is the datatype on the field numeric or text?
0
 
Rey Obrero (Capricorn1)Commented:
you will need replace function

replace([fldMfrnum]," ","")

or
just use

trim(val([fldMfrnum]))
0
 
FordraidersAuthor Commented:
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(160),"");


It was a non breaking space...?
0
 
Rey Obrero (Capricorn1)Commented:
did you try

UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
0
 
GRayLCommented:
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?
0
 
Gustav BrockCIOCommented:
Sometimes the native Trim of SQL doesn't work.
Create a custom function to call Trim of VBA:

Public Function TrimVb(ByVal strString As String) As String
  TrimVb = Trim(strString)
End Function

and use that in the query:

UPDATE tblData SET tblData.fldMfrnum = TrimVb(Nz([fldMfrnum],""));

/gustav
0
 
FordraidersAuthor Commented:
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





0
 
Rey Obrero (Capricorn1)Commented:
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

0
 
FordraidersAuthor Commented:
capricorn,  one small glitch...

It if the entry is a alphanumric or   alpha      ABC123A   OR   BUSSMANN

IT SETS THE ENTRY TO  "0"

0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
what do you want to do with this type of data

{ ABC123A   OR   BUSSMANN }
0
 
Rey Obrero (Capricorn1)Commented:
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


0
 
FordraidersAuthor Commented:
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...

0
 
Gustav BrockCIOCommented:
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
0
 
Gustav BrockCIOCommented:
Sorry, Chr():

UPDATE tblData SET tblData.fldMfrnum = Trim(Replace([fldMfrnum],Chr(160),""));

/gustav
0
 
FordraidersAuthor Commented:
cap, On your function what about capturing the   chr(160)   ?

0
 
Rey Obrero (Capricorn1)Commented:
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

0
 
FordraidersAuthor Commented:
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..

0
 
Rey Obrero (Capricorn1)Commented:
did you try the function i posted above?
0
 
Rey Obrero (Capricorn1)Commented:
place the function in a module

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

then run this update query

UPDATE tblData SET tblData.fldMfrnum = CleanString([fldMfrnum])


0
 
FordraidersAuthor Commented:
Utilizing the function ...thanks to all....
0
 
Rey Obrero (Capricorn1)Commented:
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



0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 10
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now