Convert Euros to US in Access Database

[akrshaw]

Hello!~

We have a pull that we get from JD Edwards, that has a mix of US, CDN and UK currency. I am manually having to go through each record and calculate the US dollars using .8 and 1.8 as multipliers.

I am looking for a clear path to obtaining a way to automate the conversion as the multipliers never change (don't even get me started on how inaccurate that is but everything seems to work that way here).

Any ideas are GREATLY appreciated!~

Thanks!~

 

[PaulBricker]

If you have some way of identifying which country the currency comes from, you can pull the data into a query and use a simple IIF() statement.

CurrencyConversion:IIf(Country = "CDN",.8*Amount,IIf(Country = "UK",1.8*Amount),Amount))

Paul

 

[PaulBricker]

Sorry, 1 to many )'s

CurrencyConversion:IIf(Country = "CDN",.8*Amount,IIf(Country = "UK",1.8*Amount,Amount))

Paul

 

[SgtJarrow]

Add this to the last field in your query:

ConvertedValue: IIF([CurrencyType]="US", [CurrencyValue], IIF([CurrencyType]="CDN",[CurrencyValue]*.8,[CurrencyValue]*1.8))

Replace the CurrencyType with the name of the field that holds the US, CDN, UK values. Replace the CurrencyValue with the name of the field that contains the actual amount in the base currency. Swap the .8 and 1.8 if necessary (I don't know which is correct but I used CDN*.8 to get US and UK*1.8 to get US).

Then run your query and see if it is correct.

All this assumes that you have only those THREE CurrencyTypes.

Hope it helps.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[akrshaw]

Well, the only way to determine the country is to look at the first three numbers on the business unit. So, I just created three seperate Queries that pull CDN, US and UK then do the conversion for the two fields in each query.

At the end of the day though I need them on a subform can I combine them into one query showing the three conversions for each office or do I have to put three subforms in....not even sure I can put three subforms in?

The end of the day the subform would look like this:

-----------------------------------------------------------
As of date BusUnit Prop No Total Budget $ Total Actual
11/1/07 308113 24034 $35,000 $45,000 US
11/1/07 395308 34023 $15,000 $23,000 UK
11/1/07 390434 43534 $8,000 $4,000 CDN

How can I get them to look like that with the data coming from three different queries, or can I just use one query to calculate all three?

Thanks!~

 

[akrshaw]

mstrmage1768 -

I'm a little confused by your post can you please clarify as I only see where I can specify whether it is CDN not CDN for 0.8 and UK for 1.8.

I believe with the above I am multiplying by 0.8 and then 1.8?

I have the following inside my query:

Expr1: IIf([Dept No]="308* or 311*",[Total Budget $],IIf([Dept No]="390*",[Total Budget $]*0.8,[Total Budget $]*1.8))

And all I get is an #Error?

Please advise.

Thanks so much for your help!~

 

[lespaul]

IIf([Dept No] = "308*" OR [Dept No] = "311*",[Total Budget $],IIf([Dept No]="390*",[Total Budget $]*0.8,[Total Budget $]*1.8))

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[akrshaw]

Thanks lespaul, but I'm still getting the #error message?

 

[lespaul]

what kind of field is Total Budget $?

 

[SgtJarrow]

akrshaw,

In addition to lespaul's question about the Total Budget $ field, I also would use:

Left([Dept No],3) = "308" OR Left([Dept No],3) = "311"

Notice the removal of the * and the addition of the left function. When trying to use wild cards, you would need to work with LIKE, and this is a bit more resource intensive.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[lespaul]

I thought about changing them to LIKE, but wasn't postive that was an issue....I like the LEFT better....

 

[akrshaw]

OK, now I have changed it to:

Expr1: IIf(Left([Dept No],3)="308" Or Left([Dept No],3)="311",[Total Budget $],IIf([Dept No]="390*",[Total Budget $]*0.8,[Total Budget $]*1.8))

Which actually gives me a number for the US which doesn't require a calculation because it is already USD.

I am still getting errors for the UK and CDN?

As posted above I cannot see where you are calculation the CDN by 0.8 and the UK by 1.8?

Thanks!~

 

[lespaul]

I thought you said above that the only way to tell if it was from the UK or CDN was from the DeptNo?

I don't know about anyone else, but I'm confused....can you give us a sample of the raw data that you are trying to summarize and the results you want from that data?

 

[akrshaw]

OK, let's see if I can explain better.

I have the following records that respond to one proposal

Dept No Prop No Total Budget $ Total Actual $
395085 23032 25,000 34,000 This is UK
390085 23032 10,000 5,000 This is CDN
308085 23032 5,000 15,000 This is US
311085 23032 8,000 10,000 This is US

What I need is the CDN to be multiplied by 0.8 and the UK to be multipied by 1.8.

The ONLY way to distinguish between offices is the first 3 numbers of the Dept No.

So basically I need a statement that says if Dept No 308 or 311 do not calculate, if Dept No 395 calculate by 1.8, if Dept no 390 calculate by 0.8.

I hope that helps, if not please let me know exactly what you need and I will post.

Thanks!~

 

[lespaul]

and you want to SUM the budget and actual amounts and indicate if they are US, UK or CDN?

maybe something like:

SELECT [Prop No], iif(left[Dept No], 3) IN ("308", "311"), "US", iif(left[Dept No] = "395", "UK", "CDN") As Location, SUM([Total Budget]) As Budgeted, SUM([Total Actual] As Actual FROM TableName
GROUP BY [Prop No], iif(left[Dept No], 3) IN ("308", "311"), "US", iif(left[Dept No] = "395", "UK", "CDN")

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[akrshaw]

No, not sum the amounts....just calculate the two different amounts per their conversion rate.

 

[lespaul]

and you say this query returns #ERROR# for the non-US categories?:

SELECT [Dept No], [Prop No], [Total Budget], [Actual Budget]
iif(left[Dept No], 3) IN ("308", "311"), [Total Budget],
iif(left[Dept No] = "395", [Total Budget] * 1.8, 
iif(left([Dept No] = "390", [Total Budget] * 0.8, 0))) 
As BudgetConversion,  
iif(left[Dept No], 3) IN ("308", "311"), [Total Actual],
iif(left[Dept No] = "395", [Total Actual] * 0.8, 
iif(left[Dept No] = "390", [Total Actual] * 1.8, 0))) As ActualConversion FROM TableName

 

[randy700]

Expr1:IIf(Left([Dept No],3)="308" Or Left([Dept No],3)="311",[Total Budget $],IIf([COLOR=red][b]Left([Dept No],3)="390"[/b][/color],[COLOR=blue][Total Budget $]*0.8[/color],[COLOR=purple][b][Total Budget $]*1.8[/b][/color]))

The RED code shows where you failed to use the Left function. The BLUE code indicates CDN calculation (multiplication by 0.8). The PURPLE code indicates UK calculation (multiplication by 1.8).

Randy

 

[lespaul]

found my own typos, should have caught these the first time, those $%%^@ parens!

SELECT [Dept No], [Prop No], [Total Budget], [Actual Budget]
iif(left([Dept No], 3) IN ("308", "311"), [Total Budget],
iif(left([Dept No], 3) = "395", [Total Budget] * 1.8,
iif(left([Dept No], 3) = "390", [Total Budget] * 0.8, 0)))
As BudgetConversion,  
iif(left([Dept No], 3) IN ("308", "311"), [Total Actual],
iif(left([Dept No], 3) = "395", [Total Actual] * 0.8,
iif(left([Dept No], 3) = "390", [Total Actual] * 1.8, 0))) As ActualConversion FROM TableName

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com