Ignore zero values with the Min function on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When using the Min function on a form, is there a way to have the function
ignore zero values in field which the function is working on? I have tried:


Min([CRC_] >0), but that returns a Boolean result (0 or -1). [CRC_] is a
field populated with integers and can include zero values.

Thanks for your help in advance.
 
Ok... nevermind...

I found the Dmin function and it does the job nicely.

=DMin("[CRC_]","qryAttendanceTotals","[CRC_]>0")

Thanks anyway
 
When using the Min function on a form, is there a way to have the function
ignore zero values in field which the function is working on? I have tried:


Min([CRC_] >0), but that returns a Boolean result (0 or -1). [CRC_] is a
field populated with integers and can include zero values.

Thanks for your help in advance.
Click to expand...

You'll probably need either:

- a criterion of >0 on [CRC_] to limit the query to positive values
- a DMin() function such as

=DMin("[CRC_]", "[queryname]", "[CRC_] > 0")

- or a Subquery

(SELECT Min([CRC_]) FROM queryname WHERE [CRC_] > 0)


John W. Vinson[MVP]
 
MechEngr said:
When using the Min function on a form, is there a way to have the function
ignore zero values in field which the function is working on? I have tried:


Min([CRC_] >0), but that returns a Boolean result (0 or -1). [CRC_] is a
field populated with integers and can include zero values.
Click to expand...


Since the aggregate functions ignore Null values, you can
use variations of:

=Min(IIf([CRC_] >0, [CRC_], Null))

in the form's header or footer sections.
 
Back
Top