Formatting a calculated field in a query to currency with no decimal points
I would like to know exactly where to put what, in relation to the fieldname and calculation, e.g.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
When I try and show this calculated exchange rate in my form it gives me 10 decimal places but after the first four it just gives 0's when I know there are actuals there.
I've tried various formats including General Number, Fixed etc and currently have it set as 0.0000000000 and my decimal places setting as 10.
I've even currently got my control source as:
I can work around that. However, I have a problem with my calculated fields. In one case I wind up with the following value (8360.0010000000002). I have tried a basic formatting formula but perhaps I have not entered it correctly. For the calculated fields I have the following formulas in the field rows:
Acceleration: Format([MIPR data By Participant]![Cash Award]*[MIPR data By Participant]![Acceleration Rate],"$#,##0.00")
Total: Format([MIPR data By Participant]![Cash Award] [Acceleration],"$#,##0.00")
If I could find a way of retaining my currency and 2 decimal max formats I would greatly appreciate it. Thus far I've only mentioned my problems with calculated fields but the same loss of trailing .00 and $ occur for values entered like 25000 when I want them to appear as $25,000.00.
When I took classes in Access about a decade ago, my teacher told us that if numbers we enter into our database are going to be used to do math, we should always format them as currency. She had some explanation for this which I no longer recall but which made sense at the time.
I have followed her advice, and when the numbers I'm using aren't ACTUALLY currency (they usuallyaren't), I have simply changed the decimal and leading symbol settings on the field so they display simply as numbers, not currency.
First question: Was this accurate information (that numbers to be used for math should be formatted as currency)?
If so, then
Second question: How do I get a sum field in a calculate query to display WITHOUT the currency symbol? I've never had this problem before Access 2007 but now, even though the numbers in mytable and in my regular queries display without the symbol, once total them in a Totals query, there is a currency symbol (in this case a dollar sign) in front of them and two decimal placesappear, whether or not there were decimal places in the original data.
Then I have a footer of the ID number which has a calculated field, which I want to be able to total the calculated field for all records in that group. What's the easiest way to achieve this?
example of report layout:
When I use this, it does not format my calculated fields. I know the format syntax is correct because it's used in another report that works correctly. There is no specified formatting in the query.
I've tried to add it there with no change. I've even tried to change the formatting to "Currency" and it still will not format the fields, I keep getting the calculated value out to 4 decimals, and if it's negative there is a negative sign in front of it.
For example, 153.15 should become 0015315. I can only get it as far as being 15315. Is there a way I can set Properties to format this field, or is there another expression I can add to my calculation? I'm multiplying the source field by 100 to get rid of the decimal.
Any help will be appreciated as I'm stuck on this step.