How to obtain a decimal place in the currency format

[access2010]

Current format
Data Type – Currency
Format - $#,000\.00
Decimal Places- 2
Results - $1763,00. (note the trailing decimal)

= = =
Would like - $1,763.00 (without the trailing decimal)

Thank you Nicole

 

[arnelgp]

$#,##0.00

 

[access2010]

$#,##0.00

$#,##0.00

Thank you arnelgp for your suggestion


Results using the new format
Data Type – Currency
Format - $#,000\.00
Decimal Places- 2

Results - $10.90. (note the trailing decimal)
Results - $3800.00. (could we have the comma seperator)

Can we get rid of the ( . ) dot at the end of the expression, otherwise we will accept it?

Can we get and a comma as a Thousand separator and get rid of the ( . ) dot at the end of the expression, otherwise we will accept it?

Thank you, Crystal

 

[access2010]

Arnelp, Thank you for your suggestion. Could you please suggest a way to add a , (Comma) separator for Thousands and remove the trailing Period, so that the information would be shown as $1,000.00 $6,609.39 $3,017.95 $2,768.78

Thank you Crystal

 

[Pat Hartman]

Why did you ignore @arnelgp 's suggestion? He already gave you the answer. You thanked him but didn't change your form. It is still using the incorrect expression. arnel's suggestions are pretty reliable. You need to at least try them.

 

[sonic8]

Why did you ignore @arnelgp 's suggestion? He already gave you the answer.

They didn't ignore that. They reported what happens when applying the suggestion.

@arnelgp's suggestion is basically correct. I guess that @access2010 has different Windows Regional Settings configured for his computer. I can fully reproduce the problem with German Regional Settings, where the thousands separator is a dot and decimal separator is a comma.
I get a proper currency format using: $#.##0,00

However, if one would like a proper currency format, one would just use the pre-defined format "Currency".

It is a really tough nut to force Access to use a number format different from the Windows default format but still using thousands-and decimal separators in the format expression. - No idea how that would work and whether it is possible at all.

 

[access2010]

Why did you ignore @arnelgp 's suggestion? He already gave you the answer. You thanked him but didn't change your form. It is still using the incorrect expression. arnel's suggestions are pretty reliable. You need to at least try them.

Thank you Pat for your comments, according to what the three of us in our office have reviewed, we DID follow Arnelp, suggestion, BUT are not receiving the correct Information. Please let us know what we have done wrong? Nicole

 

[access2010]

They didn't ignore that. They reported what happens when applying the suggestion.

@arnelgp's suggestion is basically correct. I guess that @access2010 has different Windows Regional Settings configured for his computer. I can fully reproduce the problem with German Regional Settings, where the thousands separator is a dot and decimal separator is a comma.
I get a proper currency format using: $#.##0,00

However, if one would like a proper currency format, one would just use the pre-defined format "Currency".

It is a really tough nut to force Access to use a number format different from the Windows default format but still using thousands-and decimal separators in the format expression. - No idea how that would work and whether it is possible at all.

Thank you sonic8 for your comments.
I normally work from our Canadian office, but am currently working from Denmark. Would this be the cause of the problem? Nicole

 

[access2010]

Thank you sonic8 for your comments.
I normally work from our Canadian office, but am currently working from Denmark. Would this be the cause of the problem? Nicole

sonic8
T.H.A.N.K. Y.O.U., THANK YOU, THANK YOU.
By changing the regional setting on my computer to Canada, my Numeric Values are now correct.
Nicole

 

[Pat Hartman]

The database you posted had the old code.

 

[sonic8]

The database you posted had the old code.

Exactly. That was the problem. Access automatically changes the new correct code, as posted by arnelgp, to the old incorrect code. There is nothing you can do about that unless you change the Regional Settings.

PS: If you want to see the effect yourself, try to use: $#.##0,00

 

[Pat Hartman]

@sonic8 How weird. Thanks for explaining that. I've always said that Access is too smart for me. We are currently in a battle of wills. In a report I have, in print preview, the first group is correct. However, when I print the report, the last value in the recordset replaces what the first group should be using. I'm not ready to post yet but you may see this strange behavior another day. Stepping through the code, the values are always correct.