Formula in a query

[F. GOMEZ]

I hope somebody could help me:
Happen that I am designing a query and I am adding 1 column that is
something like this:
Amount_US QTY Unit/Price Total
-100 5 $20.00 $ 100

In the "Total" field, I want to put a formula like this:
IF(Amount_US<0,QTY*Unit/Price*-1, QTY*Unit/Price), this formula does not
work in my query, what would be the correct way to do it.

Thanks

Fernando

 

[John Spencer MVP]

Try
IIF(Amount_US<0,-Qty*[Unit/Price],Qty*[Unit/Price])

You must have the brackets around the field named Unit/Price. Without the
brackets, Access will try to divide a field named Unit by a field named Price.
If you have fields with that name you will get an unexpected result. If you
don't have fields with that name, you will be prompted for the value for Unit
and for Price.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

I hope somebody could help me:
Happen that I am designing a query and I am adding 1 column that is
something like this:
Amount_US QTY Unit/Price Total
-100 5 $20.00 $ 100

In the "Total" field, I want to put a formula like this:
IF(Amount_US<0,QTY*Unit/Price*-1, QTY*Unit/Price), this formula does not
work in my query, what would be the correct way to do it.

Thanks

Fernando

I'm not entirely sure what you want, Fernando, but there are two obvious
errors in your expression. In Access, the function is "IIf" (Immediate If),
not "IF". Also, since your Unit/Price field as a non-standard character in
the name, you must enclose it in square brackets, like this: [Unit/Price].

Try this:

IIf(Amount_US<0,-(QTY*[Unit/Price], QTY*[Unit/Price])

But I must say that I don't understand what the Amount_US field is for, if
its sole purpose is to provide a sign (positive or negative), and the actual
amount is irrelevant.