Leading and Trailing Zero's in Query

[PC]

Hi all,

I am running a query on a table which has a couple of fields with field size
as "Double" and formatted as "Currency". I want the query to display the
results with a few leading zeros and one trailing zero and also to remove
the decimal point. E.G. [Amount] = $123.45, Query result 0000123450.

In the query I have tried Format([Amount],"0000000000") AS FormattedAmount
but this does not display anything beyond the decimal point. (Putting a
decimal point in to the Format above obviously displays one in the result
which I don't need)

Could somebody tell me what format should the [Amount] field be and what
should I use in the query to make this work.

Thanks for your help.

Paul

 

[Van T. Dinh]

Format([Amount]*1000,"0000000000")

 

[John Spencer (MVP)]

Try

multiply by 100 and append a 0 to the end.

Format(Amount*100,"000000000") & "0"

 

[PC]

Thanks John, Why didn't I think of that!

Try

multiply by 100 and append a 0 to the end.

Format(Amount*100,"000000000") & "0"

Hi all,

I am running a query on a table which has a couple of fields with field size
as "Double" and formatted as "Currency". I want the query to display the
results with a few leading zeros and one trailing zero and also to remove
the decimal point. E.G. [Amount] = $123.45, Query result 0000123450.

In the query I have tried Format([Amount],"0000000000") AS FormattedAmount
but this does not display anything beyond the decimal point. (Putting a
decimal point in to the Format above obviously displays one in the result
which I don't need)

Could somebody tell me what format should the [Amount] field be and what
should I use in the query to make this work.

Thanks for your help.

Paul