[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trim function not working

Posted on 2007-12-04
27
Medium Priority
?
651 Views
Last Modified: 2010-04-21
access 2003

This is not deleting trailing spaces   ?

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

Thanks
fordraiders
0
Comment
Question by:Fordraiders
  • 10
  • 10
  • 3
  • +2
27 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20407070
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
 
LVL 3

Author Comment

by:Fordraiders
ID: 20407084
ok...that may be it ?

how do i get rid of them.. please
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 600 total points
ID: 20407116
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(13),"");

...and...

UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(10),"");
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Author Comment

by:Fordraiders
ID: 20407184
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
 
LVL 3

Author Comment

by:Fordraiders
ID: 20407200
chr 13 and 10   not doing it... ?

Thanks
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20407247
Is the datatype on the field numeric or text?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20407271
you will need replace function

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

or
just use

trim(val([fldMfrnum]))
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 20407350
UPDATE tblData SET tblData.fldMfrnum = Replace([fldMfrnum], Chr(160),"");


It was a non breaking space...?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20407398
did you try

UPDATE tblData SET tblData.fldMfrnum = trim(val([fldMfrnum]))
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20408061
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 20409613
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
 
LVL 3

Author Comment

by:Fordraiders
ID: 20411466
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20411509
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
 
LVL 3

Author Comment

by:Fordraiders
ID: 20411525
capricorn,  one small glitch...

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

IT SETS THE ENTRY TO  "0"

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20411552
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20411558
what do you want to do with this type of data

{ ABC123A   OR   BUSSMANN }
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20411601
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
 
LVL 3

Author Comment

by:Fordraiders
ID: 20411627
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 20411643
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 20411675
Sorry, Chr():

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

/gustav
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 20413041
cap, On your function what about capturing the   chr(160)   ?

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20413118
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
 
LVL 3

Author Comment

by:Fordraiders
ID: 20415121
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20415313
did you try the function i posted above?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 20415630
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
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 31427335
Utilizing the function ...thanks to all....
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20415696
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question