Converting text field to decimal in query

[Guest]

We have a data field defined as text in order to retain the leading zeroes.
How do I get this field to display the data with two decimals? I've tried
going the currency route, but I don't want the dollar sign to be displayed.
Thanks!

 

[Guest]

Im not sure that this what you are looking for.

Try
Format([FieldName],"0000000.00")

 

[Guest]

It depends on whether you have the decimal in the text or not. If you have
the decimal in the text, you will need to remove it:
if strNbr is like "0000012.35" you need to use the Replace function to get
it out:
strNbr = Replace(strNbr, ".", "")
Now it will be "000001235"

Then use this formula:
sngNbr = Format(Csng(strNbr) / 100, "###,##0.00")
This will return 12.35

Now, the real problem is you are doing this backwards. Forget about what
the data looks like in the table. Carry the number in the table without
leading zeros. Then if you need to present it to a user in a form or report,
use the Format function to put in the leading zeros.