SQL Expression

  • Thread starter Thread starter Need Help Plz
  • Start date Start date
N

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 :)
 
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")
 
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)
 
Need said:
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)
Click to expand...


Try this instead:

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

For the negative ones:

=Max(IIf(grades < 0,grades, Null))
=Min(grades)
 
That Worked!!
Your Brilliant!
Thanks for the help- all three of you.

-----Original Message-----
Need said:
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)
Click to expand...


Try this instead:

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

For the negative ones:

=Max(IIf(grades < 0,grades, Null))
=Min(grades)
Click to expand...
 
Back
Top