Help on Iff queries.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I trying to create a query for qty sold. In the table, I have fields "qty" & "return", the value in field "qty" stored in positive value. The value in field "return" is either "TRUE" or "FALSE". If it is a sales then the value is stored as "FALSE" because it is not a return. If it is a return then the value is stored as "TRUE" because it is a return

If I have sales of 5 and return of 2 in qty, the query should show 3 which is 5 minus 2 but now it shows as 7 because the field "qty" keep all the values in positive value. How can I query the qty in (sales - return).
I tried using IIF query as shown below:

expr1: IIf([return]="TRUE",([-qty]*-1),0

It still show me the qty in positive value or 5. Pls advise

Mark Magese
 
SELECT Sum(IIf([Return]=True,[qty]*(-1),[qty])) AS Expr1
FROM Table1;

--
Wayne Morgan
Microsoft Access MVP


Mark Magesen said:
Hi,
I trying to create a query for qty sold. In the table, I have fields "qty"
Click to expand...
& "return", the value in field "qty" stored in positive value. The value in
field "return" is either "TRUE" or "FALSE". If it is a sales then the value
is stored as "FALSE" because it is not a return. If it is a return then the
value is stored as "TRUE" because it is a return.
If I have sales of 5 and return of 2 in qty, the query should show 3 which
Click to expand...
is 5 minus 2 but now it shows as 7 because the field "qty" keep all the
values in positive value. How can I query the qty in (sales - return).
I tried using IIF query as shown below:-

expr1: IIf([return]="TRUE",([-qty]*-1),0)

It still show me the qty in positive value or 5. Pls advise.


Mark Magesen
Click to expand...
 
Dear Mark:

I think what you may need would look like:

SUM(IIf(return = "TRUE", -qty, qty))

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top