Re: Trick math for Query 400

[Brendan Bispham <b.bispham@xxxxxxxxxxxxxxxxxx>]

On Thursday 27 January 2005 15:20, Greg Wenzloff wrote:
> I need a simple report to come out of Query but one column must not go
> negative.    In the define results screen this field is defined as a simple
> C = A - B but we do not want C to be negative. We would like to have a zero
> instead.   A and B are both 7 digit numbers with no decimals.
>
> Is there any trick math that would do this?

yes, essentially:
 if a>b then int(a/b) will be > 1, and int(a/b)/int(a/b)=1
 if a<b then int(a/b) will be 0, and int(a/b)/int(a/b)=0 
 multiply c with the result and you get what you want

And these are the Query defined fields:
 D  ((a+1)/(b+1))-.5       12     5
 E  d                       7     0
 F  e/d                     7     0
 C  (a-b) * f               7     0

And this is the result:
                A           B           C
 000001        10           5           5
 000002         5          10           0
 000003        10           0          10
 000004         0          10           0
 000005       250           3         247
 000006         3         250           0
 000007         0           0           0
 000008         1           1           0
 000009 9,999,999          99   9,999,900
 000010        99   9,999,999           0
 000011        10-         10-          0

> Thanks,
> Greg

Thanks, enjoyed the problem!