Need multiple currency symbols through an application - explored a few routes, suggestions please

[writeprivate]

CONTEXT


A. I am building an investment database application to process and track various small investments eg. Barclays shares (£), Microsoft shares ($)
B. Logic in place such that a "Portfolio" must use a unique 'CurrencyCode' eg. GBP, USD, EUR
C. All Purchases, Sales, BankAccount records etc. related to each Portfolio must use the Portfolio's CurrencyCode

OBJECTIVE
D. I want to display the monetary fields (say, 'Balance') on Portfolio form (and ideally table) as currency format BUT with the correct symbol (same would be true for a Purchase form belonging to the corresponding Portfolio, and so forth)

SOLUTIONS CONTEMPLATED

1. The easiest solution would be to use a general number for the various monetary fields and have a second text field with USD, GBP etc. placed to the left of the monetary field

• Rejected - this is cumbersome and feels wrong. Lots of maintenance as application grows. The numbers alone don't carry the currency with them so lots of code overhead to keep checking. Prone to error.

2. Concatenate the currency symbol to the numeric value

• Rejected - the resultant field will be a string and I will not be able to carry out calculations. this is not scalable

3. Custom format string for a monetary field eg. "balance" eg. [$USD]#,##0.00;[£GBP]#,##0.00

• Promising but can't get to work
• The idea is eg. have a fk (currency_code_id) in the Portfolio table linking to the Currency table pk - then use the custom format string on a Portfolio field eg. "balance" to do a conditional format
• I have it partly working but I cannot get the format string to work (there is no 'Custom Format' option in my version of Access so I am pasting the string directly into the Format property space)

4. Try to replicate the built-in Euro additional currency for USD (my base currency is GBP)

• Ideal solution - feels native but no idea what to do to make work - can I create another 'Euro' data type as USD
• I see the 'Create New Data Type From Fields' option but cannot seem to work out if this is helpful in trying to get a solution (I am using Access standalone 2019

Side Note: There is no need at all to have an exchange rate feature to any of this. Think that I will have two bank accounts ie. USD and GBP and they are independant. I may later do a valuation of other currencies into the base currency (based on today's spot rates) to get an overall picture but this is a bonus feature and should not drive the solution


Any suggestions would be greatly appreciated.

[orange]

You may find some insight in this youtube video on World Currency symbols by Richard Rost

Member CJ_London is an accountant. He is an experienced developer and may have focused advice.

[writeprivate]

Thanks Orange - i already watched this video but it is dealing with a different problem - that is he is keeping the base currency field and then having a seperate text field with the symbol concatenated (per my route 2). He looks up the customer's region on a current event and changes the currency symbol in that second text field.

I am looking to solve a different issue. I want fields in the table, say Portfolio table, to use the $ symbol if the Portfolio has the FK (of currency table) = "USD". That way, the setting of the Portfolio currency type will apply the correct format to the numerical values and keep them as currencies so they can be calculated on.

On my route 3 suggestion, I am trying to use Conditional Formatting on the table column with a rule to choose USD or GBP according to the custom format string and the value of the currrencycode fk. But I cannot get the Conditional Formatting rule manager to open up on the datasheet view of the table. Apparently it should?

I have also tried to insert an IIF statement in the currency format string but Access chews it all up and adds slashs "/" etc. I cannot write an expression in the table format property

[ArviLaanemets]

Why to use any format in tables at all (except for dates - you can have them as number too, but defining them as dates makes working with them easier). Ideally, users never see database tables - they have forms to work with data, and reports for communication purposes. So have your monetary values in tables as numbers, add a field for currency code, and you can continue without any headache (I myself have never used the currency format!).

[writeprivate]

see solution 1 in my original question
Having the currency indicated in a separate field means a) lot of work to have extra field everywhere, b) space that field takes up in subforms etc., c) crucially that the monetary field itself has no currency data attached to it intrinsically which could lead to expensive mistakes when inserting records to other parts of the application eg. bookkeeping. My preference is for the monetary field to have an intrinsic value ie. be related to the currency it is a member of

[ArviLaanemets]

...crucially that the monetary field itself has no currency data attached to it intrinsically which could lead to expensive mistakes when inserting records to other parts of the application eg. bookkeeping.

In bookkeeping, main thing you have to avoid, is to summarize e.g. Euros and USD's in same sum! You either must converge different currencies to single one, or to handle every currency as separate entity. Having a field in table for currency code makes this easier, not contrary

[orange]

Even for a personal/your use only application, you should not be interacting directly with tables. You should design and interact using form(s).
I'm sure others with accounting/financial application experience will offer some suggestions.

[Micron]

Let's not forget that Format function converts values to strings so that's a non-starter according to #2.
Currency format is based on regional settings and applies to all currency values. The only way to mix and match would be to use text data type. I'm no accountant so maybe that's why I cannot envision storing Euros and dollars in the same field.
Anyway, I can't see any other approach for mixed currency types but strings, and yes, you can calculate with them if you convert. Conversion would be a necessary evil given the constraints.

The message returned here is "Currency":
MsgBox TypeName(CCur(Mid("$123.45", 2)))
If there would always be a space after $ then you'd change 2 to 3, and yes, the whole thing is fraught with difficulty that I see as being unavoidable.

[writeprivate]

You said:

"In bookkeeping, main thing you have to avoid, is to summarize e.g. Euros and USD's in same sum"

On what basis are you presuming that a) any person with half a brain does not realises that, and b) that you felt that this was a useful addition rather than a distraction.

There will never be a situation where different currency values will be sum'ed. The purpose of me trying to find a solution which I would like (sorry - not one that you would like), is that I can use the currency fk in the Portfolio table to determine which records related to which currency transactions.

You said " Having a field in table for currency code makes this easier, not contrary "

This is precisely why I have stated there would be a currency code - but not next to every single monetary field - instead as a foreien key for the whole transaction

I do find it really frustrating that people come onto forum boards sometimes, just to mess with the question rather than accept the requirement at face value, and try to help (or indeed say they do not know the answer.)

[writeprivate]

Thanks as always Orange. Yes I am only interacting with forms. The purpose of finding a solution at the table level was indeed to further than stream of thought - that the currency format was set at table level and 'locked in'. The idea being that the table fk would denote the correct formating.

I am of course open to other solutions including using a current event to format on the form - but this seems high maintenance and would convert the value to a string.

It may be that I need to go with the two field solution but i was wondering if this was a common issue with a known fix.

Another consideration is that 95% of transactions will be in GBP which i why I am resisting the two field solution for what is edge cases in USD and EUR

[writeprivate]

thanks Micron - i see your point

Bearing in mind that 95% of transactions will be GBP and the USD would be edge cases, I am also contemplating another idea:

- having two fields for eg. GBP_Price and USD_Price (both currency fields in table with a custom format of $ for the USD price - this keeps the value as a number but displays the $ sign)
- then in forms, use something like If Me!currency_code_id = "USD" AND GBP_price.Value = 0 then... (redirect to a different function to perform all the operations using the USD price field) Else... (do the standard code)

[CJ_London]

I developed a db for an internet trader selling out globally through Amazon and had to deal with multiple currencies in the reports that came back. I've also developed an 'asset' manager to manage financial assets which also included transactions in different currencies

I used both your methods 1 and 2, option 3 you will find unworkable in continuous forms - in particular if you need to display the currency per the country (i.e. commas and dots have different purposes and for some countries the 'thousand' separator is not every 3 digits.

With regards your rejections

Rejected - this is cumbersome and feels wrong. Lots of maintenance as application grows. The numbers alone don't carry the currency with them so lots of code overhead to keep checking. Prone to error.

method is commonly used in many applications and is the easiest to implement. I don't see where the high maintenance comes is. And I don't see lots of code for conversion. As far as errors are concerned, that is down to the developer to ensure they can't happen.

Rejected - the resultant field will be a string and I will not be able to carry out calculations. this is not scalable

As far as carrying out calculations is concerned - depends on the length of your symbol ([USD],USD,US$, $US) but easy enough to strip out for calculations - use a function - and involves much the same amount of work as option 1.If only a single character (£,$) then you can use the val function or cCur function (which returns the value as whatever your default currency is for windows)

val(mid(currvalue,2))
cCur(mid(currvalue,2))


In the case of the amazon trader, the currency type was stored in the market record (markets were basically countries) and only used for presentation purposes when applied to transactions

In the case of the asset management, it was stored in the exchange record and also the stock/share record

Either way, not in transactions.

As an aside, both clients had separate bank accounts for each currency.

[Micron]

On what basis are you presuming that a) any person with half a brain does not realises that, and b) that you felt that this was a useful addition rather than a distraction.

We're all unpaid volunteers here and don't deserve comments like that when somebody is only trying to help. If it were directed to me you'd be on my ignore list as of now. Take that whatever way you wish but it's meant to be helpful.

[writeprivate]

I am also a contributor on various forums and understand the nature of the community and how we all help each other. I was frustrated because I showed the requsite respect and care for the community by setting out the question clearly. While it is fine of course to share your view on the approach, there comes a point where it is about 'winning' and not helping the OP but instead derailing the thread such that the OP doesnt get any help as everyone else can tend to go off topic. The point is i really don't think it was 'trying to help'. Whenever I give advice on Stack Exchange etc, I am always careful to understand the OP's question scope and perhaps offer an alternative route - but not presume disregard their requirement without any real basis. After all, i might be a chartered accountant !!

anyway, i meant no harm - probably sounds worse when reading - that line should have has a smile emoji at the end

[writeprivate]

Thank you very much for this considered reply.
I think i have established from the replies here that what i am trying to do isn't possible with Access and i think if i reflect, that is what I was trying to ascertain.

In this case, I am going with method 1

My idea is to
- switch all monetary fields to a number format
- create an unbound txt field to the left of each monetary field, and add "CURR" to the tag property of these fields
- use an on current event to loop through all form controls and find those with CURR tag
- within the loop, of this CURR subset, perform if/then logic to check the currency_code of the portfolio, and apply the "£", "$" etc value to these unbound fields
- I need to think when i post the transactions to the GL, that the currency_code is maintained - i think this should be ok as I can put the portfolio fk in each record.

Thanks