negative values to be returned as zero

[Guest]

i want to make a query in order to calculate volumes
in some causes when i make summation i have both negative and positive values
i want to get the negative values as equal to zero in the query

 

[Van T. Dinh]

Try using IIf in your expression like:

NonNegVal: IIf([YourVal] < 0, 0, [YourVal])

and then you can sum the NonNegVal

 

[Michel Walsh]

You want to consider a negative value to be zero BEFORE making the
summation, of AFTER?

before:

SELECT id, SUM(volume)
FROM somewhere
WHERE volume>0
GROUP BY id



after:

SELECT id, iif(SUM(volume)<0, 0, SUM(volume))
FROM somewhere
GROUP BY id




Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Use a calculated field

IIF(Sum([TheField])<0,0,Sum([TheField]))

In the grid:
Field: NegToZero: IIF(Sum([TheField])<0,0,Sum([TheField]))
Total: Expression


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

thank you for your help

Use a calculated field

IIF(Sum([TheField])<0,0,Sum([TheField]))

In the grid:
Field: NegToZero: IIF(Sum([TheField])<0,0,Sum([TheField]))
Total: Expression


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

i want to make a query in order to calculate volumes
in some causes when i make summation i have both negative and positive
values
i want to get the negative values as equal to zero in the query

 

[Guest]

thank you for your help

You want to consider a negative value to be zero BEFORE making the
summation, of AFTER?

before:

SELECT id, SUM(volume)
FROM somewhere
WHERE volume>0
GROUP BY id



after:

SELECT id, iif(SUM(volume)<0, 0, SUM(volume))
FROM somewhere
GROUP BY id




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

thank you for your help

Try using IIf in your expression like:

NonNegVal: IIf([YourVal] < 0, 0, [YourVal])

and then you can sum the NonNegVal

--
HTH
Van T. Dinh
MVP (Access)

i want to make a query in order to calculate volumes
in some causes when i make summation i have both negative and positive
values
i want to get the negative values as equal to zero in the query