Showing values in USD format on reports from sql currecy field in Euro

[lbannenberg]

Hello,



I've got a linked table from a SQL server where there is a column in Euro.
however the currency could be different depending on another field.

when showing this field in MS access it will always show the value in Euro.
now i need to change this field to show in USD format.

the field is showing as € 14.896,75
but i would like to show 14,896.75 $

can this be updated with a query in a temporary table?

what would be the best way to do this?

thanks,

Lucas

[Minty]

In Access what data type is the column in the linked table being detected as?
And what is it actually defined as in SQL server?

[lbannenberg]

in Access the field has datatype Currency and on the SQl server Money

[Minty]

The display of currency/money fields is driven by the regional settings of the machine the database is being used on.
So there is nothing to update as such, it's merely a local display format.

If you have field that determines the currency you can use that to format the displayed results.

Or you can use a fixed format in a query:
DisplayValue: Format(YourValueField , "$#,##0.00" )

If this is a permanent one-off change then you may be able to change the displayed currency in the access table design, even though it is a linked table.

[ArviLaanemets]

I've got a linked table from a SQL server where there is a column in Euro.
however the currency could be different depending on another field.

I.e. when in currency field is e.g. 'USD', then the amount field's real value in this row is in US Dollars. when the currency field is e.g. 'EUR', then in Euros, etc.?

when showing this field in MS access it will always show the value in Euro.
now i need to change this field to show in USD format.

For table, you either
1. set the format displaying currency, and all entries are displayed in this currency format (which means when the real currency is different, this value is wrong, as the amount was not converted);
2. convert all amounts to currency you will use;
3. instead of monetary format for amount field, use standard numeric, with 2 decimals. In table structure, place currency field as previous/next to amount field.

the field is showing as € 14.896,75
but i would like to show 14,896.75 $

How many thousands of years are needed to pass by, to € 14.896,75 = 14,896.75 $ to happen?

what would be the best way to do this?

You mean in report?
Simply combine the numeric value of amount and the string representing the currency into string like '14,896.75 $' or € '14.896.75', depending on currency for this row (NB! Use your own decimal delimiters for alphanumeric part of string!), and in report use this string instead.
Or in report, simply use amount formatted as standard numeric, and add the currency string before/after it.
Or convert all currencies to one which you declare as standard one for you, and use those converted values in your report in whatever format you want.