Forums

Forum Home

Support

Foxfire! 8+ Support

Formatting Text...

Formatting Text field to Currency

6/3/2011 7:10:20 AM

DOF

3 posts

Formatting Text field to Currency

I have a text field, payrate that should display 115,125.00. It is displaying 115.00. The format statement is:

VAL(TRANSFORM(VAL(LEFT(paypdata.payrate,LEN(paypdata.payrate)-2)+"."+RIGHT(paypdata.payrate,2)),"99,999,999.99")).

This has been working. I deleted and re-entered the statement but nothing changed.

Any ideas ?

Thanks

6/6/2011 2:14:36 AM

AndrewM

149 posts

www.aksel.com

Re: Formatting Text field to Currency

The problem is with the ",".

If you do a STRTRAN(paypdata.payrate,","), that will remove it.

You would get a better result with

VAL(STRTRAN(paypdata.payrate,","))

6/7/2011 6:38:12 AM

DOF

3 posts

Re: Formatting Text field to Currency

I've tried the solution but without success. The original data field has no commas, the string is 11512500. Do I need to include the 99,999,999.99 formatting ?

8/8/2011 1:17:15 AM

AndrewM

149 posts

www.aksel.com

Re: Formatting Text field to Currency

If the original value is a numeric value, why are you attempting to convert it into a string and then back into a numeric value?

If 11512500 is really 115125.00, then the way to convert it to a currency value would be 11512500/100.
If the 11512500 is stored as a string "11512500", then VAL("11512500")/100.

If you wanted to DISPLAY it as a currency value,then the previous transform would work:

TRANSFORM( 11512500/100,"$ 99,999,999.99")

Page 1 of 1

Support

Foxfire! 8+ Support

Formatting Text...