Topics Search

Formatting a calculated field in a query to currency with no decimal points

Formatting a calculated field in a query to currency with no decimal points
Views: 64
I have a calculated field in a query which I want to format to Currency to 0 decimal points, either in the query or in the form which the query serves. I have an idea that it is something like Format$ but have not been able to guess how to apply it.

I would like to know exactly where to put what, in relation to the fieldname and calculation, e.g.

FieldName:[Calculation])
Sponsored Links:

More topics

 

Formatting a calculated field in a query to currency with no decimal points

Preview
I have a calculated field in a query which I want to format to Currency to 0 decimal points, either in the query or in the form which the query serves. I have an idea that it is something like Format$ but have not been able to guess how to apply it. I would like to know exactly where to put what, in relation to the fieldname and calculation, e.g.

FieldName:[Calculation])
 

Fill my decimal places with 0's when I know there are real numbers?

Preview
I have an exchange rate field which is calculated to ten decimal places. This is looked up from a date field and a currency field via a select query which when I test that shows to 10 correct full decimal places.

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:
=FormatNumber(ExchangeRate([Start Date],[CurrencyID]),10)
 

Currency formats lost between Query and Word in Mail Merge

Preview
I am having trouble getting my currency format out of an Access 2010 Query into a Word 2010 document. If it were just a case of dropping the .00 and not including the currency icon ($ in this case) that would be okay.

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.
 

Rounding of number not desired

Preview
I am trying to make two calculated fields come out looking the same in a query. When I hover the mouse cursor over them one has a value 48.9932 and the other has a value of 48.9974. When the mouse cursor is moved away they round off and display as currency as $48.99 and $49.00 respectively. In the calculated formula for each of them there are some 3 decimal place multipliers used. Could this be causing the problem. I must have the 3 digit multipliers in the formula. I have tried changing number types, decimal places, etc in the underlying table, but nothing seems to prevent them from displaying this way. In the calculated formula for each of them there are some 3 decimal place multipliers used. Is there a way to make them come out even by ignoring the 3rd and 4th decimal place
 

Number or Currency Formats in Access - Which to Use?

Preview
I am using MS Access 2007 on Windows 7.

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.
 

Data Type In Calculated Field

Preview
Select query pulls in two fields from same table, both are currency type fields. Then I have a calculated field in the query finding the difference between the two, but the output is not currency. Is there some way to make the calculated field a currency data type
 

Currency Format on Sum Text Box shows decimal instead of comma

Preview
I have a calculated sum field in the report footer of a report. I have it set for format currency with 2 decimals; however, for the number 1234.56 it displays it as $1.234.56 instead of $1,234.56. What do I need to do to have it show the comma opposed to the decimal next to the number
 

calculated field from calculated field?

Preview
I have a report that is grouped by an ID number as a header, then a detail section displaying that relates to the ID number. There are two currency fields, and a calculated field whichdisplays the sum of the two currency fields for each record.

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:
 

Formatting on report

Preview
I have a simple report generated from a query. The query has two calculated fields. On the report I want to display these fields in a specific format using this formatting:
#,###.00;(#,###.00);0.00

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.
 

Number conversion

Preview
I need to convert a number that is currently formatted as Double to a 7-digit format without decimail points. Using a query, I can create the calculated field to get rid of the decimal point, but I need for the field to be 7 digits long, with leading 0s to make up all 7 digits.

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.