max function

[davetek]

hey,

i am trying to replicate the excel max function....
is it possible in SQl to mimic this...with excel you can say max(field1,field2)...this will give the highest value of the 2 fields.....is there a similar sql function or do you have to use the if function

cheers

 

[zrzr]

If your numbers are stored into a table, max works in SQL.
This is a simple example :

SELECT max(nameofthefield)
From nameofthetable;

 

[davetek]

what i actually want to say is
the max of field1 and zero.....
i think using an if clause is the only way around it....

max(field1,0)

 

[PaulBricker]

They don't have a built in function to do this. You would have to send the record to a function. To use this, you would put this code in a VBA Module.

Function RMax(ParamArray FieldValues()) As Variant
                  Dim lngMax As Variant
                  Dim varArg As Variant
                  
                  
                  lngMax = 0

                  For Each varArg In FieldValues
                  If varArg > lngMax Then
                  lngMax = varArg
                  
                  End If
                  Next

                  RMax = lngMax
                
 End Function

Then in a new column of your query you would put

MyMaxValue:RMax(FieldName1, FieldName2, ...FieldNameX)


Paul

 

[davetek]

cheers paul...

 

[PaulBricker]

To just compare two numbers it would be
MyMaxValue:IIf(Field1 >0,Field1,0)


Paul

 

[zrzr]

So, in your case, you can une iif function :

SELECT IIf([nombre]>0,[nombre],0) AS Expr1
FROM Table1;

It'll show the number if it is higher than 0, and 0 else.