Data Report & SQL

[SSB]

I have a problem where I have to produce a report from a transaction file with a total of all negative values in one column and a total of all positive values in another. I have been hitting my head against a brick wall trying to find an SQL expression that will do it.

Can anyone help?

[brucevde]

What database? If SQL Server then you could use the Case statement.

Select 
	Sum(Case When qty < 0 Then qty Else 0 End) as NegSum,
	Sum(Case When qty > 0 Then qty Else 0 End) as PosSum
From ...

[SSB]

I am using the Jet database. Tried using Case but it wasn't recognised. Have tried again using IIf() and seems to work. Many thanks - i'd spent many hours on that one!