Max Records

[vbjohn]

I need to have a report that shows a max number of hours of 8500. If the number is more than 8500 then put that record at 8500 else print out the number.

I am creating a report in Visual Basic's Data Reporter with Sql as the backend.

 

[tlbroadbent]

Use a CASE statement as in the following example.

Select
Col1, Col2,
Case
When col3<=8500 Then col3
Else 8500
End As col3
From table1 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[vbjohn]

CASE is not supported in the Datareporter in VB6.

 

[karluk]

Try the following. The arithmetic should return col3 as long as it's less than 8500. Otherwise you should get 8500. It's not too elegant, but it appears to work.

Select
Col1, Col2,
((1-sign(col3-8500))*col3 + (sign(col3-8500)+1)*8500)/2 as col3
from table1