Hi all,

How can I do this in Access query? It say it has violation problem. But I really need to use iif function in this update statement. How can I workaround that? thanks!

update t_header as h
set h.price = iif(h.FX=1, h.amount/h.price, h.fx*(h.amount/h.price))
where h.Refno='1'
and format(h.dealingdate,'dd/mm/yyyy')=#13/02/2009#

Perhaps I am misreading your IIF statement, but isn't this:

set h.price = iif(h.FX=1, h.amount/h.price, h.fx*(h.amount/h.price))

identical to this:

set h.price = h.fx*(h.amount/h.price)

?

Or is h.FX different from h.fx?


Duluter

Perhaps I am misreading your IIF statement, but isn't this:

set h.price = iif(h.FX=1, h.amount/h.price, h.fx*(h.amount/h.price))

identical to this:

set h.price = h.fx*(h.amount/h.price)

?

Or is h.FX different from h.fx?


Duluter

thanks for your reply.
Actually, the formula is not the concern, my concern is how update can be used with iif function.

I don't think SQL supports IIF. You may need to evaluate the iif statement in VBA before running your query.

Try making a simple function to evaluate the iif statementput in a general VBA module

Some air code that may help you
Function CalculatePrice(Amount as single, Price as currency,fx as single ) as Currency
if fx= 1 then
calculateprice = Amount/price
else
calculateprice = fx * (amount/price)
end if

end function


Then your query would look like

update t_header as h
set h.price = Calculatepricef( h.amount,h.price, h.fx)
where h.Refno='1'
and format(h.dealingdate,'dd/mm/yyyy')=#13/02/2009#

I am still confused.

Why are we testing if fx = 1? If it equals 1, then

(amount/price) is equivalent to fx * (amount/price)

Isn't it?

So why not just strictly use fx * (amount/price) and eliminate the iif altogether?


Duluter

I am still confused.

Why are we testing if fx = 1? If it equals 1, then

(amount/price) is equivalent to fx * (amount/price)

Isn't it?

So why not just strictly use fx * (amount/price) and eliminate the iif altogether?


Duluter
You are absolutely correct. I don't know why he is making it more complicated than it needs to be. but hey a lot of newbies want to fly before they can run:D

but hey a lot of newbies want to fly before they can run

That pretty much sums up my life path as well. :)


Duluter