SQL Expression

[Need Help Plz]

I have a field (grades) of numbers ranging from -100
(negative 100) to 100 (positive 100) in a table. I want
to return the Max positive number, Min positive number
and Max negative number, Min negative number for a bound
field in a report. This is what I have tried-

Max positive number (Control Source):
=Max([grades])

Min positive number (Control Source):
=Min([grades]>0)

etc...
Plz help

 

[Ken Snell]

Max positive number:
=DMax("Grade", "tblName", "[Grade]>0")

Min positive number:
=DMin("Grade", "tblName", "[Grade]>0")

Max negative number:
=DMin("Grade", "tblName", "[Grade]<0")

Min negative number:
=DMax("Grade", "tblName", "[Grade]<0")

 

[John Spencer (MVP)]

The following return null if there is no grade matching criteria.

Maximum Positive Grade:
Max(IIF(Grade>=0,Grade,null)

Minimum Positive Grade:
Min(IIF(Grade>=0,Grade,Null)

Maximum Negative Grade (Closest to zero -1 is larger than -50:
Max(IIF(Grade<=0,Grade,Null)

Minium Negative Grade (Closest to -100 -1 is larger than -50:
Min(IIF(Grade<=0,Grade,Null)

 

[Marshall Barton]

I have a field (grades) of numbers ranging from -100
(negative 100) to 100 (positive 100) in a table. I want
to return the Max positive number, Min positive number
and Max negative number, Min negative number for a bound
field in a report. This is what I have tried-

Max positive number (Control Source):
=Max([grades])

Min positive number (Control Source):
=Min([grades]>0)

Try this instead:

=Max(grades)
=Min(IIf(grades > 0,grades, Null))

For the negative ones:

=Max(IIf(grades < 0,grades, Null))
=Min(grades)

 

[Guest]

That Worked!!
Your Brilliant!
Thanks for the help- all three of you.

-----Original Message-----

I have a field (grades) of numbers ranging from -100
(negative 100) to 100 (positive 100) in a table. I want
to return the Max positive number, Min positive number
and Max negative number, Min negative number for a bound
field in a report. This is what I have tried-

Max positive number (Control Source):
=Max([grades])

Min positive number (Control Source):
=Min([grades]>0)

Try this instead:

=Max(grades)
=Min(IIf(grades > 0,grades, Null))

For the negative ones:

=Max(IIf(grades < 0,grades, Null))
=Min(grades)