Format currency to exact decimal points

[johnseito]

Could someone advise me on how to format currency to the exact decimal points in sql queries.

For example,

Format. ( ( (very big number/100). * another very big number), ‘currency’) as resultNUM

the code on top is rounded off to the two decimal points. When you do that to many rows of numbers the total vs the original total is off by a couple cents.

Please advise !

[ranman256]

FORMAT does not do exact, if the # is 5 decimals, the # will round.

do you want rounding, or truncating?

[Micron]

you could try FormatCurrency function. Don't know if it does any rounding or not; never used it. Best you look it up as it involves regional settings if you don't provide all the parameters.

[johnseito]

ranman256 &Micron,

Thank you for the feedback.
The goal is to have the number show as is without rounding or reducing it two 2 decimal points but still only show two decimal points with the $ sign (kind of like excel we can see two decimal points but can expand and show more decimal point). So when the value is exported to excel there is no difference with the value being off by a couple of cents when we decide to sum the numbers and compare to the original data. This would be ideally what we want.

However if the first way can’t be done, we can go with rounding it by two decimal points, in the third decimal point anything that is 5 or more gets to add to the second decimal points but still have the $ sign.

[Micron]

I don't see why you'd worry about formatting on the Access side. FormatCurrency function will allow more than 2 decimal places. Then format to 2 places in Excel. If I'm not mistaken, formatting in Excel will cause rounding anyway, so don't round (if that's the case) there either unless you don't care.

[johnseito]

I used the FormatCurrency in Access and is giving 2 decimal points.

Sorry this is my formula:

Result =
Formatcurrency( (number in decimal points but suppose to be in percentage/ 100) * very big number)

The result of that is
$ #.###,###.##

When I exported into excel it only has two decimal points, although I thought there would be more decimal points.

I sum the result by rows, let’s say you have 10 the result is off by a couple of cents vs the original value.

However if I do it in excel with the same formula.

(number in decimal points but suppose to be in percentage/ 100) * very big number

I have many decimal points, 4, 5 etc and when I add the 10 result rows together it equals to the original value perfectly to the cents.

How can I do the same in access query ? Please advise.

[Micron]

you forgot to specify the decimal places. As I wrote, if you don't provide the optional parameters, they default to your regional settings. No doubt for you that is 2 decimal places.

example: formatcurrency((123469.1245/100)*456789.36925,4)
result: $563,993,835.0220

[CJ_London]

What about using the ccur function? Also be aware excel is a law unto itself as to how it represents its data

[Micron]

Could work too, methinks. However I don't believe you can specify a decimal place count so it might depend on what level of precision you need. It ought to return $563,993,835.022 but won't tell you if that's .0220 or .0216. The latter would be the rounded up decimal place. Time for OP to weigh in?

[CJ_London]

the currency datatype is 4 dp. The issue may be around rounding to 2dp in which case use the round function.

[johnseito]

Micron, this is good.

But in the form can it be 2 decimal places.
It would be weird if the value is currency and in the form have like 3 or more decimal places.

$ 5,232.032

Instead of

$ 5,232.03

The second issues is when download it into excel, the formatcurrency that you gave is as text format in excel. Need to convert it back to value. Is there a way I can convert it in Access and have dollar signs and 2 decimal points in forms but when extracted to excel is as values or numbers with the dollar sign. Thx

[davegri]

I suggest using Decimal and specify scale and precision properties in the table. Forms and reports could show 2 decimal places, while the table can hold many more.
Precision is the total number of digits allowed in a number, including left and right of the decimal.
Scale is the number of digits allowed to the right of the decimal.


So 1234567.89 has a precision of 9.
123456.789 has a precision of 9 and a scale of 3.


A precision of 4 and scale of 2 would result in a number with a maximum value of 99.99


Percent designations like 125.5% would require precision 4 and scale of 3 (not one) since it is actually stored as 1.255.


Precision has a maximum value of 28, but textboxes in forms allow only a max of about 15 decimal places.

[Micron]

I took from your first post that you wanted to store more precision

the code on top is rounded off to the two decimal points. When you do that to many rows of numbers the total vs the original total is off by a couple cents.

So many people post here without really understanding what format means, which might be the case here. Formatting is really about how data LOOKS not about what's behind the "mask". You can format $5,012.4517 as 5012.45 (note that I dropped the dollar sign, thousands separator and 2 decimal places) or most any other compatible result that you want. Sometimes the format is just as important as the value behind it; sometimes not.

Is there a way I can convert it in Access and have dollar signs and 2 decimal points in forms

Not sure I get the question. If it's in Access as currency, there is no conversion to be done in Access - only formatting. If you want to store more than 2 decimals in a table but only show 2 in a form, you format the form control. If number formats cause rounding and you don't want that, then sometimes formatting as text is the answer.

To illustrate further, do you know what today's date really is? It is 43530. The format is 03/06/2019.

Your original post said nothing about going between Excel and Access. That's another whole can of worms, assuming the expression even means anything to anyone these days.
Since you just mentioned going in both directions, I'm not sure what to make of your statement. Data movement from Excel to Access has it's own set of issues. It's generally accepted that approximately the first 11 rows from an Excel column dictates how Access receives the formatting. If there's any text, the field becomes text. Going the other way, people tend to use templates when possible so that formatting is preserved.

I would be certain that Excel needs to be involved in the process. Simple calculations can be handled by Access forms. If workbooks are required, then a simpler solution would be workbook macros that fix the formatting and/or data type in Excel. A complicated solution would be Automation between Access and Excel.