custom currency symbol independent of regional settings

preamble: The database I am working on is based on many companies each having
its own home/base currency. Moreover, a user can be logged into two company
sessions on the same computer at the same time, one having $ and the other
having euro as the base currency in the respective sessions. The database
system must now create new companies with indonesian and middle eastern
currencies. I need to attack the problem of currency symbols independent of
the regional settings using code and work arounds perhaps.

outcome sought: the ability to format values in the controlsource property
whilst retaining the output as a number and not a string. The following
produces a string which stops all further calculations.

#1. controlsource string for textbox text1:
=Format([text1], "\R\p#,##0.00;-\R\p#,##0.00")

#2. text1.format = "\R\p#,##0.00;-\R\p#,##0.00" --- when the textbox's
format property is targeted, the textbox's value retains its numberic
characteristic and can be used in calculations.

BUT when the controlsouce (as in #1. above) is formated with a literal
other than a $ or EURO (ie if it is not currently selected in the regional
settings) then the output is a string and further calculations are not
possible.

Before I started work on the database, a lot of reports and textbox
controlsources had been formatted for currency. The problem is that
formatting in the controlsource overrides any changes in the format property.

Is there a way to get the format function to understand that the literal
symbols used for currency are indeed currency symbols and therefore not
convert the output to a string. Using functions like mid and val to extract
the currency symbols and use the number part for calculations is not an
option.

Alternatively can one leave the format Property as "Currency" and change
the currency symbol used by Access. Is there something like Access's own
locale which may be set to the operating system's locale or stay independent
of it.

Any other line of thinking is welcome in order to achieve currency
formatting outside the format property and have the resulting output retain
it's numeric characteristic without any post-processing needed for it to be
so.

On Thu, 4 Feb 2010 23:23:01 -0800, CodeCola
wrote:

I might have two currency fields: only to show for human consumption,
and another one (perhaps hidden) with the same value to use for
calculations.

-Tom.
Microsoft Access MVP


preamble: The database I am working on is based on many companies each having
its own home/base currency. Moreover, a user can be logged into two company
sessions on the same computer at the same time, one having $ and the other
having euro as the base currency in the respective sessions. The database
system must now create new companies with indonesian and middle eastern
currencies. I need to attack the problem of currency symbols independent of
the regional settings using code and work arounds perhaps.

outcome sought: the ability to format values in the controlsource property
whilst retaining the output as a number and not a string. The following
produces a string which stops all further calculations.

#1. controlsource string for textbox text1:
=Format([text1], "\R\p#,##0.00;-\R\p#,##0.00")

#2. text1.format = "\R\p#,##0.00;-\R\p#,##0.00" --- when the textbox's
format property is targeted, the textbox's value retains its numberic
characteristic and can be used in calculations.

BUT when the controlsouce (as in #1. above) is formated with a literal
other than a $ or EURO (ie if it is not currently selected in the regional
settings) then the output is a string and further calculations are not
possible.

Before I started work on the database, a lot of reports and textbox
controlsources had been formatted for currency. The problem is that
formatting in the controlsource overrides any changes in the format property.

Is there a way to get the format function to understand that the literal
symbols used for currency are indeed currency symbols and therefore not
convert the output to a string. Using functions like mid and val to extract
the currency symbols and use the number part for calculations is not an
option.

Alternatively can one leave the format Property as "Currency" and change
the currency symbol used by Access. Is there something like Access's own
locale which may be set to the operating system's locale or stay independent
of it.

Any other line of thinking is welcome in order to achieve currency
formatting outside the format property and have the resulting output retain
it's numeric characteristic without any post-processing needed for it to be
so.