Removing leading zeros from a text field

I am using MS Access currently because it allows quick prototyping of database applications.  You can easily link tables to SQL databases and generate reports and queries.  One issue I had lately was a text field containing a number with leading zeros.  After writing a loop to remove the zeros I discovered this simple way of achieving the desired result.

Replace( LTRIM(Replace( fldName, "0", " ")), " ", "0")

Here is a description of whats happening starting with the inner part of the statment and assuming fldName contains “0001205” :-

Description fldName
the inner replace() replaces all zeros with space,
there are three spaces before 12 5
”   12 5″
ltrim() removes all spaces from left side,
notice the space is left between 2 and 5
“12 5”
the outer replace() replaces all spaces with zeros “1205”

This should work for all visual basic implementations such as ms access, excel, word, etc.

Advertisements

2 thoughts on “Removing leading zeros from a text field

  1. Stacy

    THANK YOU!! I felt so stupid for not being about to come up with this on my own. I was using all the same functions in the SQL but just didn’t think of your strategy of replacing 0s with “” first. Thanks again.
    Stacy

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s