how can I trim leading zeros from a number in a text field for dis

G

Guest

I have a text field which holds a street number with leading zeros for
sorting. I would like to trim the leading zeros to display on a form.
 
J

John Vinson

I have a text field which holds a street number with leading zeros for
sorting. I would like to trim the leading zeros to display on a form.
Click to expand...

How about an alternative? Store the number as you would like it
displayed, and sort by Val([address]).

That said... here's some VBA code to trim leading zeros from any text
string:

Public Function TrimZeros(strIn As String) As String
Dim iPos As Integer
For iPos = 1 To Len(strIn)
If Mid(strIn, iPos, 1) <> "0" Then Exit For
Next iPos
TrimZeros = Mid(strIn, iPos)
End Function

Use =TrimZeros([address]) as the control source of your form control.

John W. Vinson[MVP]
 
S

Stefan Hoffmann

hi Cam,
It worked well for me too when it is numeric. What function do I use for
text values.
I need to trim off all spaces at the end of a text. Thanks
Click to expand...
Take a look at LTrim(), RTrim() and Trim().


mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

validation rule for exactly 5 digits, including leading zeros 0
Trim leading zeros 3
Export of text field trims leading zeros 1
Removing leading zeros 2
Access 2003 - Joining number and text data 3
Remove leading zeros 2
Restore leading zero 3
Droping Leading Zeros In Table 4

Top