Access query to convert currency's

Posted on 2012-09-12
Medium Priority
Last Modified: 2012-09-13
I have a need if possible for an access query to convert currencies.
The data is a follows (this is simplified)

Column: Ref     Currency amount1 amount2 amount3
Data     :a123    Eur          100          125       85
             b765    GBP         250          75         0
             A324    sek           500          500     15000

I would like to convert all this amounts to GBP. So Im guessing that I need to somehow check the currency field, then apply a formula that converts those amounts to GBP. I was thinking that adding a table called convrates, that list all the possible currencies in the DB along with the conversation rate and then somehow link this and convert the amounts
Question by:George_Milton
  • 2
  • 2
  • 2
LVL 77

Accepted Solution

peter57r earned 800 total points
ID: 38390275
I would suggest that you do not change the original values; just show the converted values as well as the originals.

Your idea is sound.

A currencies table is what you want.  It must hold the currency name in the same spelling as your data.  And as you say a conversion rate.

You have to match the conversion rate with the formula you are going to use..
If you want to convert by multiplying ...
GBPConverted= OriginalAmount * ConversionRate
then you must enter into the currencies table the numbe of pounds for 1 originalCurrency unit
So for 1 Euro you would enter something like (as as 21sept2012)  0.7994.

You create a query using your data table and the tblCurrency and match on Currency name.  Then in the query you just create new columns for the converted values.
For example...

Author Comment

ID: 38390574
That works just as I expected, thank you. Just one last question, is there any way to add a parameter that the currency if matched is asked for the conversion rate. I have been told that some of the amounts are not done at the current rate.  So for example the query is run, it matches a currency and asks for the rate.
LVL 77

Expert Comment

ID: 38391140
That's not going to be possible in a query.  It would require a VBA procedure to work out which currencies to ask for and interact with the user to get those values.

But surely the whole idea is that the rate in table is the one you should be applying?  Otherwise what is the point of the table?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 31

Assisted Solution

hnasr earned 400 total points
ID: 38391421
If you run the query from a form, you may check the rate and update it if required.

A combo box holds the currencies and the current rates from convTable.
A textbox displays the currency rate and allows for modification, and the query runs. The edited value will replace the old currency rate in the convTable.

Author Closing Comment

ID: 38394975
Thanks, great help
LVL 31

Expert Comment

ID: 38395685

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question