Merging numbers

Oct 31, 2014 at 2:19 PM
Edited Jul 18, 2016 at 4:21 PM
I have a spreadsheet with calculated figures, e.g.

=A1*.035

The spreadsheet is set to display two decimal places. The PDF field is set to number format, with two decimal places.

But no matter what I do, the formats are not followed and instead of getting a nice, clean format like
45,734.75
I instead get
45734.7465

What do I need to do to get comma separated numbers with two decimal places?
Coordinator
Oct 31, 2014 at 3:28 PM
As far as I can tell, the Access Database Engine does not allow me to access "formatting" data on number fields. I've solved this in the past by forcing the excel column to "text" as opposed to "general" or "number". In your case, since it's a calculated value, you may need to use the TRUNC() function in your equation to force a certain number of decimal places. It's not ideal, but I tried looking for ways to access the column/cell data format and can't find anything.
Oct 31, 2014 at 4:29 PM
Edited Oct 31, 2014 at 5:12 PM
TRUNC() won't do it since I need the numbers rounded, so I used ROUND(). Still, the numbers are printing without regard to how they are displayed on the spreadsheet. I tried text and it didn't help eether. Rats.
Jan 19, 2015 at 6:21 AM
Using the TEXT function works nicely in getting the clean format. However, you have to paste back the result of the TEXT function as Values so that the contents of the cell that is merged to the PDF form file is not a formula or number but text. Hope this helps.