SELECT MAX(field) FROM table BUT MAKE SURE IT IS NOT LESS THAN 2000

How can I select the maximum value from a field, but make sure that the
value i want stays greater than a cetrain value?

I currently have this:

SELECT
A.rating,
B.rating,
1/(1+10^((A.rating-B.rating)/400)) AS Expected,
Round( -20*( [A].[rating]+[b].[rating] ) / (
SELECT MAX(rating) FROM teams
) + 50) AS K
FROM teams AS A, teams AS B
WHERE (A.teamid<>B.teamid)
ORDER BY A.rating DESC , B.rating DESC;

This works perfectly, except that the "SELECT MAX(rating) FROM teams"
sub query is currently returning 1000, and that value should never be
les than 2000. Im not trying to say that at least one team should be
ranked above 2000, but rather that in order to calculate rankings, I
need a value of at least 2000 for my maximum.

This is based on the ELO system, in case you are wondering.

Hopefully,
John "otac0n" Gietzen

re: SELECT MAX(field) FROM table BUT MAKE SURE IT IS NOT LESS THAN 2000

otac0n wrote:[color=blue]
> How can I select the maximum value from a field, but make sure that the
> value i want stays greater than a cetrain value?
>
> I currently have this:
>
> SELECT
> A.rating,
> B.rating,
> 1/(1+10^((A.rating-B.rating)/400)) AS Expected,
> Round( -20*( [A].[rating]+[b].[rating] ) / (
> SELECT MAX(rating) FROM teams
> ) + 50) AS K
> FROM teams AS A, teams AS B
> WHERE (A.teamid<>B.teamid)
> ORDER BY A.rating DESC , B.rating DESC;
>
> This works perfectly, except that the "SELECT MAX(rating) FROM teams"
> sub query is currently returning 1000, and that value should never be
> les than 2000. Im not trying to say that at least one team should be
> ranked above 2000, but rather that in order to calculate rankings, I
> need a value of at least 2000 for my maximum.
>
> This is based on the ELO system, in case you are wondering.
>
> Hopefully,
> John "otac0n" Gietzen[/color]

Your subquery to get the MAX(rating) can be used within the IIf
function.

Try:

Round(-20 *([A].[Rating]+[b].[Rating]) / IIf((SELECT MAX(rating) FROM
teams) < 2000, 2000 + 50, (SELECT MAX(rating) FROM teams) + 50)) AS K

or

Round(-20 *([A].[Rating]+[b].[Rating]) / (IIf((SELECT MAX(rating) FROM
teams) < 2000, 2000, (SELECT MAX(rating) FROM teams)) + 50)) AS K

James A. Fortune
CDMAPoster@FortuneJames.com

re: SELECT MAX(field) FROM table BUT MAKE SURE IT IS NOT LESS THAN 2000

Thanks.

But, is IIF part of SQL or VBA?

re: SELECT MAX(field) FROM table BUT MAKE SURE IT IS NOT LESS THAN 2000

otac0n wrote:[color=blue]
> Thanks.
>
> But, is IIF part of SQL or VBA?[/color]
[color=blue]
>From the A97 help file for IIf:[/color]

You can also use the IIf function (immediate if) in a calculated
control on a Microsoft Access form or report. You can use the IIf
function to evaluate an expression and return either of two other
values, depending on whether the expression evaluates to True (-1) or
False (0).
[color=blue]
>From the Example:[/color]

You can enter the following expression in the ControlSource property of
a calculated control.

= IIf([OrderAmount] > 1000, "Large", "Small")
[color=blue]
>From Specifics:[/color]

In Visual Basic, the IIf function evaluates both truepart and
falsepart, even though it returns only one of them. In a Microsoft
Access form or report, however, the IIf function evaluates either
truepart or falsepart, whichever is appropriate. Therefore, you need
not be concerned about the undesirable side effects of evaluating both
arguments if you use the IIf function in a calculated control, query
expression, or macro.

In Visual Basic, the more full-featured If...Then...Else statement
offers greater versatility.


So the answer to your question seems to be that IIf can be useful in a
ControlSource or in a SQL query or in a macro, but is not recommended
for use in control flow when writing VBA code. E.g.,

If IsNull(Me!txtFirstName.Value) Then
strMergeName = Me!txtLastName.Value
Else
strMergeName = Me!txtFirstName.Value & " " & Me!txtLastName.Value
End If

is preferred over

strMergeName = IIf(IsNull(Me!txtFirstName.Value), Me!txtLastName.Value,
Me!txtFirstName & " " & Me!txtLastName.Value)

I hope this answers your question,

James A. Fortune
CDMAPoster@FortuneJames.com