Help on Iff queries.

[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

 

[Wayne Morgan]

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

--
Wayne Morgan
Microsoft Access MVP

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 Magesen

 

[Tom Ellison]

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