Expression drops leading zeros - how can I prevent this?

[avegrrl]

I have a fixed-width text file that I import daily and split into a bunch of fields. For two pairs of fields, I concatenate the values to create a dollar amount. E.g. | 123 | 45| becomes $123.45. For one (and only one) pair of fields, the second entry is dropping any leading zeros. So instead of $123.01, the query is resulting in a value of $123.1, which reformats to $123.10.

The one that IS working has the dollar field set as a number and the Cents field set as text with two digits. Modeling the broken one after that doesn't work. I can get the table to display correctly by setting both fields to Numbers with a format of 00 for cents, but the results of the query are still wrong.



The query is: Expr1: [Dollars] & "." & [Cents]

Any ideas? Sorry if that was a convoluted question.

[ketbdnetbp]

avegrrl -

Don't know if this will help but, you could try...

Adding a new expression field to the query, like...

ACents: IIF(Len([Cents])=1, "0" & [Cents],[Cents])

and then reference that expression in the other expession...

Expr1: [Dollars] & "." & [ACents]

Untested but, may be worth a try.

All the best,

Jim

[ssanfu]

What you describe makes sense. When Access (or Excel) imports a string into a number field, both programs will drop leading zeros because they "think" the value shoud be a number, not text.


Jim's solution should work. You could also combine the two columns into one column in the query:

Expr1: [Dollars] & "." & IIF(Len([Cents])=1, "0" & [Cents],[Cents])



I would probably import the dollars and cents data as text. Then the original expression would work with no changes:

Expr1: [Dollars] & "." & [Cents]


IF you needed to change the string to a number, you could use:

Expr1: CCur([Dollars] & "." & [Cents])


Many ways to skin a cat...

[avegrrl]

Thank you both. Adding the IIF to the Expression worked perfectly!