Change Field €€€.cc

[Paula Martinez]

Hello,

I would appreciate some help with a problem I am trying to solve.

I have a table with the total sales of some products and i need to change the field format.

Original Values:
13


140,1
1299,55

The values of the total sales need to have the following format: €€€.cc (point in the decimal separator)

Output Example:

13.00
140.10
1299.55

I created a query with the formula to change the format of the field but is not working.

Any suggestions would be greatly appreciated


Thank you in advanced.

[CJ_London]

I presume the field is numeric and not text? if numeric perhaps use the format property for the field? not clear why you are talking about the € symbol when you don't want it to appear

format property would be set to #0.00. If it is supposed to be currency, change the datatype to currency

note it is a bad idea to format fields in a table as it can hide the true underlying value. worry about it instead in your from or report. Also, not a good idea to store calculated values such as totals, calculate them when required

[Paula Martinez]

Hello Ajax,

Yes the field of the table i need to change is numeric. I tried to create a query with a formula to change the format but I cannot find the correct format.

The € symbol it was not the best way to explain what I pretend. I have a numeric field with a comma in the decimal separator and I need to change it to a point separator, additionally I need to have two decimals places:


Example: 140,1 need to appear as 140.10, 13 need to appear as 13.00

Thank you in advanced

[Minty]

Try using CCur(YourFieldName) it recognises most separators, based on your local preferences.

[Paula Martinez]

Hello Ajax,


I tried to use CCur(Total) and unfortunately it does not work


The result is: 13,00 € / 140,10 € / 1.225,45 €
I need to have: 13.00 / 140.10/ 1225.45


Thank you for the help.

[orange]

Your database works for me without the currency symbol --but I am in Canada and have different regional settings.

The € sign may come from the customized Currency symbol in your regional settings. May adjust and see if problem persists.

[Minty]

It wasn't Ajax that you answered, Your local settings appear to be set to use , as a decimal separator.

Try DecimalFormat : Format(CDbl([total]),"#0.00")

[CJ_London]

As I suggested, use the format property



However I suspect your decimal separator is specified as a comma rather that a full stop in your language settings. If so click on the windows icon on the app bar at th bottom, type 'language settings'. Select region - at the bottom select additional regional settings then 'change date, time or number formats and change the decimal symbol. This will affect all apps tho' so may not be ideal

If this is the case, then you would need to convert the number to a string, then replace the comma with a dot

TotalTxt: Replace(Format([total],"Fixed"),",",".")

but this will then be a string so you cannot manipulate as a number (such as summing)

[mike60smart]

Hi Paula

In the design of the table just change the properties of the Total field to that shown in the attached.