Supporting multiple currencies in access form

[NJMike64]

All,

I've been trying to find an answer to this, but so far have not. I have 3 fields in my db, valueUSD, ValueOther, Currency. Obviously, valueUSD will always be shown in the US Dollar format $#,###.## however, I want to create VBA code so that when the user selects a specific currency (i.e. GBP or EUR), the number in ValueOther is formatted using the correct currency symbol.

Any suggestions?

Thanks
Mike

[orange]

And what level of accuracy do you hope to have and maintain /retain?
There are online currency exchanges that may offer some help, but exchange rates vary with time.
Perhaps you could tell us more of the proposed application.

[NJMike64]

Thanks for your reply. I am not looking for the program to do the actual currency conversion. The user will enter in the specific amount, I just want the value field to format the number with the correct currency indicator, when they indicate GBP the number is prepended with £ and if they select EUR, the value is prepended with €. So it is really how do I change the format of the field based on the currency selected.

Thanks
Mike

[Rawb]

The Currency default format is controlled by your computer's Locale. If you want to use something other than the default, then you will have to build it using the Format() Function or use a custom Input Mask Property on the Form Controls.

[NJMike64]

Thanks Rawb. I have tried using the Fromat() command in VBA, keep getting mismatch errors. Here is the test code;

If [currency] - "GBP" Then
[valueother] = Format([valueother], "£#,###.##")
End If

valueother is set as currency in the table format.

Thanks
Mike

[Rawb]

That should be an equals sign, not a minus

If [currency] = "GBP" Then
[valueother] = Format([valueother], "£#,###.##")
End If

[NJMike64]

Sorry, that was a copy typo to here. It is an equal sign, but still getting a runtime error -2147352567 (800200009)

Thanks

[rpeare]

is [valueother] a bound control? if it is it has to be a text field or it won't accept the value you're trying to add. If you're storing a number value you would set the INPUT MASK to the string with the british pound sign in it.

FORMAT changes a number to a string you just want it to display a certain way, not actually be stored as a string.

[NJMike64]

Thanks. Yes that works, but I need it to store in the data table with the right currency symbol. Sounds like that is not going to be an option here.

[rpeare]

why would you need to actually store it with the symbol? you can display it in any output (queries, forms, reports) with the correct symbol.
the bottom line is that if you store the symbol you're storing a text string, not a number.

[NJMike64]

ok, I understand. I just like to have it stored once instead of going through the formatting and additional unbound fields.

Thanks
Mike