Criteria for Non-Negative Numbers

[guthrie.23]

Hi,

I need a criteria expression that will take all negative numbers and show a zero instead of the negative number.

Here is my field: DaysLate: ([DueDate]-[DateReturned])

This gives me positive and negative numbers, as well as zero.
When I try to enter a criteria, I get the Enter Parameter Value Box and it is asking me for DueDate. What am I doing wrong?

Thanks.

 

[rainman89]

use an iif statement

DaysLate: iif([DueDate]-[DateReturned] <0, 0, [DueDate]-[DateReturned] )

 

[guthrie.23]

Im still getting the Enter Parameter Box, and then its asking for DueDate

 

[rainman89]

is duedate a field in your table?

 

[guthrie.23]

Yes it is a field.
DueDate: ([DateRented] +5)

DateRented is in short date form (11/24/2010)

 

[rainman89]

ok, so its not an actual field in the table then.

Try

DaysLate: iif(([DateRented] +5)-[DateReturned] <0, 0, ([DateRented] +5)-[DateReturned] )

if duedate is the name of a field on your form, the previous entry I had would work...

Are you sure the name of the field is dueDate?

 

[boblarson]

Ray wrote:

is duedate a field in your table?

You wrote:

Yes it is a field.
DueDate: ([DateRented] +5)

DateRented is in short date form (11/24/2010)

So DueDate is NOT a field in your TABLE it is a calculated field in the QUERY. Big difference there. Sometimes you can't use a calculated field directly in another calculated field in a query. So you have to save that query that has that DueDate and then put that into another query so you can use it as a field to calculate using the IIF statement.

 

[guthrie.23]

Perfect, thanks a lot.

 

[boblarson]

Or you have to use the full calculation as Ray showed in the post just before I posted

 

[boblarson]

I seem slow today - I need a nap.

 

[rainman89]

I seem slow today - I need a nap.

It's Friday... have one!