Largest In A Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to know if there is a Function i could use in VBA which will
return the largest value from a range of dates, numbers etc. In Oracle there
is a function called GREATEST which has the format
GREATEST(expression1,expression2,expression3...) and will return the largest
value.

Is something similar in Access or will I have to write my own function to do
this

Thanks
 
-----Original Message-----
I would like to know if there is a Function i could use
Click to expand...
in VBA which will >return the largest value from a range
of dates, numbers etc.

There's a couple.

In a form you can set a control's ControlSource to =Max
([FieldName])

This will return the maximum value in the field
specified. The field must be part of the form's
underlying recordset.

There is also a Domain Aggregate Function call DMax()

This is a little slow but can return the maximum value
from a table or query.

i.e.

=DMax("[MyField]", "[MyQuery]", "[Field] = 'London'")
 
Hi,

Thanks for your reply but I was trying to find the largest from a list of
values and not a field as I understand I could use Max and DMax.

My problem is that I have three fields containing dates in a Table and I am
trying to determine the largest one without having to do If Then comparisons
etc.

Thanks


SJ McAbney said:
-----Original Message-----
I would like to know if there is a Function i could use
Click to expand...
in VBA which will >return the largest value from a range
of dates, numbers etc.

There's a couple.

In a form you can set a control's ControlSource to =Max
([FieldName])

This will return the maximum value in the field
specified. The field must be part of the form's
underlying recordset.

There is also a Domain Aggregate Function call DMax()

This is a little slow but can return the maximum value
from a table or query.

i.e.

=DMax("[MyField]", "[MyQuery]", "[Field] = 'London'")
Click to expand...
 
-----Original Message-----
My problem is that I have three fields containing dates
in a Table and I am trying to determine the largest one
without having to do If Then comparisons
Click to expand...

That reads more like a design problem in that you have
not properly normalised your table structure.
 
You would need to write a function to do this. Access has nothing built-in.
Here is a sample that works with numbers. You should be able to modify it to
work with Dates. Possibly all you need to do is to change the IsNumeric to
IsDate and then force vMax to return a date with the CDate function.



'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function
 
is it possible to use this function to get the 4 largest values in a range?
How to rewrite the function?
Franky
 
A function could be written, but where are you going to use it? If you were
expecting to use it in a query, you would probably need to return a string with
some kind of separator (at least if you want to use it in one column).

I would probably, put the values into an array and then sort the array then grab
the top 4 items to return in a string or an array or...

If you need the four largest values in a range, I suspect that you really need
to restructure your data. Can you describe why you need to get the four
largest values.

What version of Access are you using?

Anyway, this UNTESTED AIRCODE may work for you.

Public Function fGetTopN(IntHowMany As Integer, ParamArray vArray() As Variant)
'Call fGetTopN (4,4,22,7,8,9,0,8,3) should return a string "22,9,8,8"
Dim i As Integer, j As Integer, Low As Integer, Hi As Integer
Dim Temp As Variant
Dim a() As Variant
Dim vReturn As String

Low = 0
Hi = UBound(vArray)
ReDim a(Hi)

For i = Low To Hi
a(i) = vArray(i)
Next

j = (Hi - Low + 1) \ 2
Do While j > 0
For i = Low To Hi - j
If a(i) > a(i + j) Then
Temp = a(i)
a(i) = a(i + j)
a(i + j) = Temp
End If
Next i
For i = Hi - j To Low Step -1
If a(i) > a(i + j) Then
Temp = a(i)
a(i) = a(i + j)
a(i + j) = Temp
End If
Next i
j = j \ 2
Loop

For i = Low To Hi
vArray(i) = a(i)
Next

If IntHowMany > Hi Then IntHowMany = Hi + 1

For i = Hi To 1 + Hi - IntHowMany Step -1
vReturn = vReturn & "," & vArray(i)
fGetTopN = vReturn
Next i

End Function
 
Back
Top