return largest value?

**Allen Browne** · Jul 17 '08, 02:15 PM

Re: return largest value?

See MaxofList() here:

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

Access doesn't have any such functions, because having many repeating fields
is not a relational database design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CRCI@NorthStat e.netwrote in message
news:d2ju74l69g 765e2h6bianthim qarkj856g@4ax.c om...

Is there any built-in FN for returning largest value
in a group of say 2 - 10 values? Lets say I want
a FN something like MaxVal(5, 7, 9, 3, 12) or
perhaps MaxVal(79,34). I would wanna see
return value of 12 and 79 respectively.
>
What's simplest way to do that in A97?

**JvC** · Jul 17 '08, 02:15 PM

Re: return largest value?

Put the values in an array, and bubble sort it. I found the following
BubbleSort routine online. ToSort is your array of values. You may find
one you like more!

Sub BubbleSort(ToSo rt As Variant, Optional SortAscending As Boolean =
True)
' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
' By Chris Rae, 19/5/99. My thanks to
' Will Rickards and Roemer Lievaart
' for some fixes.
Dim AnyChanges As Boolean
Dim BubbleSort As Long
Dim SwapFH As Variant
Do
AnyChanges = False
For BubbleSort = LBound(ToSort) To UBound(ToSort) - 1
If (ToSort(BubbleS ort) ToSort(BubbleSo rt + 1) And
SortAscending) _
Or (ToSort(BubbleS ort) < ToSort(BubbleSo rt + 1) And Not
SortAscending) Then
' These two need to be swapped
SwapFH = ToSort(BubbleSo rt)
ToSort(BubbleSo rt) = ToSort(BubbleSo rt + 1)
ToSort(BubbleSo rt + 1) = SwapFH
AnyChanges = True
End If
Next BubbleSort
Loop Until Not AnyChanges
End Sub

After serious thinking MLH wrote :

Is there any built-in FN for returning largest value
in a group of say 2 - 10 values? Lets say I want
a FN something like MaxVal(5, 7, 9, 3, 12) or
perhaps MaxVal(79,34). I would wanna see
return value of 12 and 79 respectively.
>
What's simplest way to do that in A97?

**JvC** · Jul 17 '08, 02:15 PM

Re: return largest value?

I like Allen's routine better!

JvC explained :

Put the values in an array, and bubble sort it. I found the following
BubbleSort routine online. ToSort is your array of values. You may find one
you like more!
>
Sub BubbleSort(ToSo rt As Variant, Optional SortAscending As Boolean = True)
' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
' By Chris Rae, 19/5/99. My thanks to
' Will Rickards and Roemer Lievaart
' for some fixes.
Dim AnyChanges As Boolean
Dim BubbleSort As Long
Dim SwapFH As Variant
Do
AnyChanges = False
For BubbleSort = LBound(ToSort) To UBound(ToSort) - 1
If (ToSort(BubbleS ort) ToSort(BubbleSo rt + 1) And
SortAscending) _
Or (ToSort(BubbleS ort) < ToSort(BubbleSo rt + 1) And Not
SortAscending) Then
' These two need to be swapped
SwapFH = ToSort(BubbleSo rt)
ToSort(BubbleSo rt) = ToSort(BubbleSo rt + 1)
ToSort(BubbleSo rt + 1) = SwapFH
AnyChanges = True
End If
Next BubbleSort
Loop Until Not AnyChanges
End Sub
>
>
After serious thinking MLH wrote :

>Is there any built-in FN for returning largest value
>in a group of say 2 - 10 values? Lets say I want
>a FN something like MaxVal(5, 7, 9, 3, 12) or
>perhaps MaxVal(79,34). I would wanna see
>return value of 12 and 79 respectively.
>>
>What's simplest way to do that in A97?

**MLH** · Jul 17 '08, 02:55 PM

Re: return largest value?

Well, I don't know about the serious thinking part
of it, but here's where I was headed...
Function BiggestThing(a As Variant, b As Variant, Optional c As
Variant, Optional d As Variant, _
Optional e As Variant, Optional f As Variant, Optional g As Variant,
Optional h As Variant, _
Optional i As Variant, Optional j As Variant, Optional k As Variant,
Optional l As Variant, _
Optional m As Variant, Optional n As Variant, Optional o As Variant,
Optional p As Variant, _
Optional q As Variant, Optional r As Variant, Optional s As Variant,
Optional t As Variant) As Variant
'************** *************** *************** *************** *************** ***************
' Idea here is to accept up to 20 values and return the biggest one,
' using the default sorting method set up in Access.
'************** *************** *************** *************** *************** ***************
On Error GoTo Err_BiggestThin g

100 Dim arr1 As Variant
120 Dim i2 As Integer, j2 As Integer, k2 As Integer

140 arr1 = Array(a, b, c, d, e, f, g, h, i, j, l, m, n, o, p, q, r, s,
t)
'160 i2 = arr1(0)
180 j2 = arr1(0)
200 For k2 = 1 To UBound(arr1)
220 If IsNull(arr1(k2) ) Then Exit For
240 If arr1(k2) j2 Then j2 = arr1(k2)
260 Next
280 Debug.Print j2

It was kind-a-workin' ... But in the loop, when I
encountered the first Null array value - for some
reason the Exit For is not catching it. Can you
figure why?

I'm gonna have a look now at both your suggestion
and Allens. Thx 4 the feedback.

On Thu, 17 Jul 2008 07:10:33 -0700, JvC <johnvonc@earth link.net>
wrote:

>Put the values in an array, and bubble sort it. I found the following
>BubbleSort routine online. ToSort is your array of values. You may find
>one you like more!
>
>Sub BubbleSort(ToSo rt As Variant, Optional SortAscending As Boolean =
>True)
' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
' By Chris Rae, 19/5/99. My thanks to
' Will Rickards and Roemer Lievaart
' for some fixes.
Dim AnyChanges As Boolean
Dim BubbleSort As Long
Dim SwapFH As Variant
Do
AnyChanges = False
For BubbleSort = LBound(ToSort) To UBound(ToSort) - 1
If (ToSort(BubbleS ort) ToSort(BubbleSo rt + 1) And
>SortAscendin g) _
Or (ToSort(BubbleS ort) < ToSort(BubbleSo rt + 1) And Not
>SortAscendin g) Then
' These two need to be swapped
SwapFH = ToSort(BubbleSo rt)
ToSort(BubbleSo rt) = ToSort(BubbleSo rt + 1)
ToSort(BubbleSo rt + 1) = SwapFH
AnyChanges = True
End If
Next BubbleSort
Loop Until Not AnyChanges
>End Sub
>
>
>After serious thinking MLH wrote :

>Is there any built-in FN for returning largest value
>in a group of say 2 - 10 values? Lets say I want
>a FN something like MaxVal(5, 7, 9, 3, 12) or
>perhaps MaxVal(79,34). I would wanna see
>return value of 12 and 79 respectively.
>>
>What's simplest way to do that in A97?

>

**MLH** · Jul 17 '08, 03:05 PM

Re: return largest value?

Darn straight! You are right. Allen's FN's are awesome.
I wasn't familiar with the ParamArray thingie. Allen's put
some great solutions out on this forum over the years.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xx

On Thu, 17 Jul 2008 07:11:58 -0700, JvC <johnvonc@earth link.net>
wrote:

>I like Allen's routine better!
>
>JvC explained :

>Put the values in an array, and bubble sort it. I found the following
>BubbleSort routine online. ToSort is your array of values. You may find one
>you like more!
>>
>Sub BubbleSort(ToSo rt As Variant, Optional SortAscending As Boolean = True)
> ' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
> ' By Chris Rae, 19/5/99. My thanks to
> ' Will Rickards and Roemer Lievaart
> ' for some fixes.
> Dim AnyChanges As Boolean
> Dim BubbleSort As Long
> Dim SwapFH As Variant
> Do
> AnyChanges = False
> For BubbleSort = LBound(ToSort) To UBound(ToSort) - 1
> If (ToSort(BubbleS ort) ToSort(BubbleSo rt + 1) And
>SortAscendin g) _
> Or (ToSort(BubbleS ort) < ToSort(BubbleSo rt + 1) And Not
>SortAscendin g) Then
> ' These two need to be swapped
> SwapFH = ToSort(BubbleSo rt)
> ToSort(BubbleSo rt) = ToSort(BubbleSo rt + 1)
> ToSort(BubbleSo rt + 1) = SwapFH
> AnyChanges = True
> End If
> Next BubbleSort
> Loop Until Not AnyChanges
>End Sub
>>
>>
>After serious thinking MLH wrote :

>>Is there any built-in FN for returning largest value
>>in a group of say 2 - 10 values? Lets say I want
>>a FN something like MaxVal(5, 7, 9, 3, 12) or
>>perhaps MaxVal(79,34). I would wanna see
>>return value of 12 and 79 respectively.
>>>
>>What's simplest way to do that in A97?

>

**MLH** · Jul 17 '08, 03:05 PM

Re: return largest value?

Awesome help. Hat's off to you, Allen. Thx.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxx

On Thu, 17 Jul 2008 22:07:12 +0800, "Allen Browne"
<AllenBrowne@Se eSig.Invalidwro te:

>See MaxofList() here:

Microsoft Access tips: MaxOfList() and MinOfList() functions

http://allenbrowne.com/func-09.html

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

>
>Access doesn't have any such functions, because having many repeating fields
>is not a relational database design.

**lyle fairfield** · Jul 17 '08, 09:55 PM

Re: return largest value?

MLH <CRCI@NorthStat e.netwrote in
news:a7nu74taps 8u63mq02av76e96 us12g2kgq@4ax.c om:

Darn straight! You are right. Allen's FN's are awesome.
I wasn't familiar with the ParamArray thingie.

What would be the advantage of using a parameter array rather than a single
array? Which method would be easier to call from other code?

**MLH** · Jul 18 '08, 04:05 PM

Re: return largest value?

On Thu, 17 Jul 2008 21:49:06 GMT, lyle fairfield <lylefa1r@yah00 .ca>
wrote:

>MLH <CRCI@NorthStat e.netwrote in
>news:a7nu74tap s8u63mq02av76e9 6us12g2kgq@4ax. com:
>

>Darn straight! You are right. Allen's FN's are awesome.
>I wasn't familiar with the ParamArray thingie.

>
>What would be the advantage of using a parameter array rather than a single
>array? Which method would be easier to call from other code?

I guess it's the idea that you can have as many or as few
arguments as you wish. I could have, say, written the procedure
with 20 arguments in a single array. But then, 6-weeks from next
Tuesday, I might have needed to feed a 30-item list to the FN.
There's an element of elegance in the simplicity of his FN's that
I like too. I think the ease of calling either method is the same.

return largest value?

return largest value?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment