turning some values into negative numbers

[tillmill]

Hello

I have a table with the following columns:
"Person A", "Person B" (both are "text"-types) and "Value" (which is of the currency-type).

When building a query, I want to have all lines where for example "Peter" is the value of either Person A or Person B. I got it this far already.

But now comes the tricky part: If "Peter" was the value of Person A, I just want to copy the line with the query. But if Peter was the value of Person B, I want to copy the line BUT change the number of the "Value"-column of that line to the negative. (for example make -52.00Euro out of 52.00Euro)



example
-------

this is the raw data on the table


Person A, Person B, Value
Peter, Michael, 10.00 Euro
Peter, Claus, 12.00 Euro
Claus, Michael 52.13 Euro
Michael, Peter, 18.00 Euro



now my query first takes each line with Peter, so this is the outcome:

Peter, Michael, 10.00 Euro
Peter, Claus, 12.00 Euro
Michael, Peter, 18.00 Euro
---------------
sum: 40.00 Euro





and this is what I actually want to get:


Peter, Michael, 10.00 Euro
Peter, Claus, 12.00 Euro
Michael, Peter, -18.00 Euro
--------------
sum: 4.00 Euro






It probably needs some "if person a = peter then ..." but I'm not familiar with the SQL commands so I'd be grateful for any help.

 

[Scooterbug]

in a query, you would use an IIF statement, in your case:

Value: IIF([PersonB]="Peter",(IIF[Value]<0,[value],[value]*-1),[Value])

This checks to see if PersonB is Peter. If so, it checks the value to see is less than zero. If less than zero, then it multiplies it by -1, which makes it a negative.

 

[tillmill]

The value is always positive, it just has to show it as negative and work with that negative number, so I guess I can leave out the second IF statement.

I tried it like this now:

IIF([PersonB]="Peter",[value]*-1,[value])


But it tells me that the marked comma in red is wrong. "wrong syntax"


edit: Of course I also tried your line with both if statements but it gives me the same error.
edit2: Where exactly do I have to add this line?

 

[Scooterbug]

Hmmm...I just made a quick table with the data you provided and based a query off it with the IIF statement and it worked for me. Can you post the exact SQL statement you are using?

 

[tillmill]

Works like a charm, thanks.

I entered the line into the criteria cell instead of the field-cell.