Removing Leading Zeros 2

[derekstewart]

I have a query I am trying to run and it is not matching due to leading zeros of different lengths (sometimes 1 other times 2-4). How can I remove these leading zeros? Thanks.

 

[PHV]

Any chance you could post some more specific info, like the query you tried and some input input examples with expected result ?
If the field(s) with leading zeos is all numeric, you may consider the CLng function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

I assume that the field is a text field. Perhaps it contains something like employee numbers or SaleIDs.

A good method to fix the problem would be to contatenate more zeros to the data, and then use the right() function.

Lets say that you have "01", "001", and "0001" in a field. the expression x:right("00" & fieldname,4) would make all of these values into "0001".

I tested this approach and I must admit that it did not work perfectly. However, I have used it in other languages, and I think that some variation of it would work in Access sql.

 

[DrSimon]

DrSimon (IS/IT--Management) Aug 23, 2004
You can use Format here, e.g. Format([Text],"#.#") . This will leave any text string starting with a non-numeric as string but tidy up anything that is numeric. If you want it to be an integer don't use Int unless all the inputer text is numeric as it will error, use Format([Text],"#") instead.
Simon Rouse

 

[jasonmac]

DrSimon,

You don't know how much time you just saved me. I've been looking for this answer all morning. Thank you, thank you, thank you!