Fomat currency question

[pazgb]

Hello,

Im stuck and need some help formatting a field in my append-query.

Example:

50.00

Need it to be at least 7 digits with leading zeros and no decimals

So it would be "0005000"

Also the field in my table were I need to store this value is TEXT.

Does anyone know if this is possible?

Mike

 

[traingamer]

One way:

Right("0000000" & Trim(Str([Amount]*100)),7)

(this assumes that you have two decimal places).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[pazgb]

Thanks traingamer, but I do have values with 1 decimal place, will this work for that also?

Here are some examples of what I have for values and what I need them to look like:

Old Needed
146.36 0014636
145.24 0014524
6.1 0000610
13.1 0001310
93.63 0009363

 

[pazgb]

I also have negitive txn's. So

Right("0000000" & Trim(Str([Amount]*100)),7)

will not work, sorry I forgot to include this in the request.

I do not want negitive sign included in the 7 digits. I have a seprate indicator field to define positive or negitive.

Old Needed
146.36 0014636
145.24 0014524
6.1 0000610
13.1 0001310
93.63 0009363
-15.15 0001515
-100.45 0010045

 

[traingamer]

...I do have values with 1 decimal place, will this work for that also?

It should be fine.

For the negative sign, try:

Right("0000000" & Trim(Str([COLOR=blue][b]Abs([/b][/color][Amount]*100[COLOR=blue][b])[/b][/color])),7)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

Or just

Format (Abs([Amount]) * 100, "0000000")

 

[PHV]

Format(Abs(100*[Amount]),"0000000")

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

 

[traingamer]

Much simpler!

 

[pazgb]

Thanks everyone. Works great!