multi if then Expression as query column using input parameters

  • Thread starter Thread starter Stuff3
  • Start date Start date
S

Stuff3

I want to multiply the value in one column by one of 3 values (2 input as a
parameter). I am not sure how to tackle it.
On opening the query the user inputs a dollar rate as parameter dollar and a
euro rate as a parameter euro. I need a column C in the query to hold a value
calculated by multiplying a figure in colomn B with a value that is dependent
on if £ or $ or E is shown in column A. So if A=£ then C = B* 1. if A=$ then
C=B*dollar rate and if A=E then C=B*euro rate.

Please advise how to do this.
 
Stuff3 said:
I want to multiply the value in one column by one of 3 values (2 input as a
parameter). I am not sure how to tackle it.
On opening the query the user inputs a dollar rate as parameter dollar and a
euro rate as a parameter euro. I need a column C in the query to hold a value
calculated by multiplying a figure in colomn B with a value that is dependent
on if £ or $ or E is shown in column A. So if A=£ then C = B* 1. if A=$ then
C=B*dollar rate and if A=E then C=B*euro rate.
Click to expand...


The calculated field in the query could be like:
C: IIf(A="$", B * DollarRate, IIf(A="E", B * EuroRate, B))

But the really big question is how do you determine the
DollarRate and EuroRate.
 
First, I would use a form to enter the value for your Euro and Dollar rates.
By using the a form, you can ensure that the values meet specific validation
criteria, which you cannot do with a parameter query that counts on input
boxes to enter your parameters.

Then, in your query, you would refer to the forms textboxes for the
parameter values. Something like:

SELECT iif(yourtable.[A] = "E", [Forms]![frmExchRates].txtEuro,
[Forms]![frmExchRates].txtDollar) * yourtable.
FROM yourTable
 
Thanks Dale. Marshall's reply did the job but you have raised an interesting
point regarding validation so I might give your idea a go when I have more
time.
--
cheers
Stuff3


Dale Fye said:
First, I would use a form to enter the value for your Euro and Dollar rates.
By using the a form, you can ensure that the values meet specific validation
criteria, which you cannot do with a parameter query that counts on input
boxes to enter your parameters.

Then, in your query, you would refer to the forms textboxes for the
parameter values. Something like:

SELECT iif(yourtable.[A] = "E", [Forms]![frmExchRates].txtEuro,
[Forms]![frmExchRates].txtDollar) * yourtable.
FROM yourTable

----
HTH
Dale



Stuff3 said:
I want to multiply the value in one column by one of 3 values (2 input as a
parameter). I am not sure how to tackle it.
On opening the query the user inputs a dollar rate as parameter dollar and a
euro rate as a parameter euro. I need a column C in the query to hold a value
calculated by multiplying a figure in colomn B with a value that is dependent
on if £ or $ or E is shown in column A. So if A=£ then C = B* 1. if A=$ then
C=B*dollar rate and if A=E then C=B*euro rate.

Please advise how to do this.
Click to expand...
Click to expand...
 
Stuff3 said:
Thanks Dale. Marshall's reply did the job but you have raised an interesting
point regarding validation so I might give your idea a go when I have more
time.
Click to expand...


That's part of the big question I mentioned.

Do you really want users to be entering exchange rates on
the fly? How often do the rates you use change? The
international exchange markets are constantly changing so
it's unlikely you want to try to keep up with that, OTOH,
you probably want to change them fairly often.

Another part of this issue is what rates do you want to use
if you run a query for last week's transactions? How do you
expect users to remember the rate they used when they ran
the query at some point in the past? To preserve historical
accuracy, it's usually best to have an exchange rates table
with fields for the currency identifyer, date the rate was
effective and the exchange rate for that date. This way,
one person would be responsible for entering rates whenever
needed and all other users would not know or care about
rates.
 
Back
Top