Query Leading zero's on format command 2

[mel41g]

I am trying to make a field in a query that is pulling a number amount from a table. Some of the number's include decimal places, some do not. I need the new format of the information to be 12 characters long, with no decimal places, however, the 2 right-most numbers of every record must represent decimal places. The number needs to be right aligned, with leading zeros. Any sugestions on how to do this?

 

[PHV]

SELECT Format(100*[yourField],'000000000000') As FormattedAmount

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

 

[SkipVought]

Hi,

...2 right-most numbers of every record must represent decimal places

Are you going to do arithmetic on this new column?

If NOT then just create a string.

 Left("0000000000000",5-Len(Int([field]*100))) & Int([field]*100)

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

Hi,

...2 right-most numbers of every record must represent decimal places

Are you going to do arithmetic on this new column?

If NOT then just create a string.

 Left("0000000000000",12-Len(Int([field]*100))) & Int([field]*100)

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[mel41g]

I've got it. Thanks for your quick response!

 

[SkipVought]

PHV,

Why didn't I think of such simplicity! ==> *

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}