Negative number is Null criteria in my query

[SheilaAlighieri]

Hi all!

I noticed an error in one of my query's. The following gives the formulas I entered in my field name.

Rest Budget: [Budget]-[Committed]-[Invoiced]
Committed: [BudgetCommitted]-[InvoicedCommitted]

Ok, here is my problem. When the value of the committed field is negative. My Rest Budget formula adds up this number. But I don't want that happen

So under the field Rest Budget I need to define a criteria of some sort. It should be something like this:

If Committed < 0 then Is Null

But I don't know how to enter this in my query. Can anyone solve this (probably easy) problem?

Thanks,

Sheila

 

[SgtJarrow]

Use:

Rest Budget: [Budget]-IIf([Committed]<0,0,[Committed])-[Invoiced]

This says if committed is less than 0 use 0, otherwise use the committed value.
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[tlbroadbent]

Try this.

Rest Budget: [Budget]-IIf([Committed]<0, 0, [Committed])-[Invoiced]
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[mikevh]

How do you want [Committed] counted if it's negative?
I think there'll be a problem if it's Null, as you're
trying to do math with it. Would zero be okay? In
that case, [Rest Budget] will end up equal to
[Budget] - [Invoiced] (since Committed gets counted as
zero).

If zero is an acceptable value, you'd say it like:
Rest Budget: [Budget]- iif([Committed] < 0, 0, [Committed])-[Invoiced]

 

[SheilaAlighieri]

Thanks guys.. but maybe you're right and I shouldn't use Is Null. Is it possible to invert the negative number to a positive in the calculation?

For instance when Committed = -15
It would be used as if it was = +15

In that case the negative committed amount would still be subtracted instead of added up.

I know my question must sound a bit stupid..

Sheila

 

[mikevh]

Use the Abs function. This will always return a
positive (or zero) value.

Rest Budget: [Budget]- Abs([Committed])-[Invoiced]

 

[SheilaAlighieri]

Super! Thanks!

Sheila