Leading and Trailing Zero's in Query

  • Thread starter Thread starter PC
  • Start date Start date
P

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
 
Try

multiply by 100 and append a 0 to the end.

Format(Amount*100,"000000000") & "0"
 
Thanks John, Why didn't I think of that!

John Spencer (MVP) said:
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
Click to expand...
Click to expand...
 
Back
Top