Hi,

I am trying to show the minimum value in a column, but if the minimum value is zero, I want the next lowest value. For example, if zero if the minumum and 5 is the next lowest, I want the query to return 5 as the value. Any help would be great. I tried entering >0 in the criteria but the query simply removed the records that had zero as the minimum. Thanks in advance!

-Sarah

Try using the Min function in a Group By query

Select Min(YourField as MinField), YourPrimaryKey
From YourTable
Group By YourPrimaryKey
Having YourField > 0;

That is what I tried but the records that had zero as the minimum were excluded from the query.

How do you want this to work? You want the min>0 as the first record displayed, or the last? You would need to have your query assign a sort order.

SortOrder: IIf([yourfield]<=0,999999,[yourfield])

Do you want it colored? Do you want it to stand out separate? (Use a form)

How this works is that I have several trips and each trip has several lengths and I want the query to show the minimum length for each trip (this is how it works now), but if 0 is the minimum length, I want the minimum value to actually be the lowest value above zero. Sorry if this is confusing. I don't think a SortOrder function will do the trick.

See if this gets you there, two ideas.

1. ShortTrip: iif([yourfield]<=0,dmin("[yourfield]","yourquery","[yourfield] > 0"),[yourfield])


2. This might excecute more quickly:
A) Make a totals query to find the minimum above zero, such as you did above.
B) Create another query to join that query back to the previous query on the appropriate field(s)
C) Create another field in the new query:
ShortTrip: IIf([yourfield]<=0,[MinOfYourfield],[yourfield])

Both of these should do the same thing. The idea is to replace all zeros (and below, if that's possible) with the minimum value above zero.

Thanks!