Max Field

[dmyoungsal]

I have 4 unbound fields that I use to enter Zones (range of Miles). the fields are cboZone1, cboZone2, cboZone4 and cboZone4.

Most times the 2-4 fields will be blank and it is highly possible the zones will not be entered in any particular order. They will most likely be entered lowest first, largest last.

I need to find (and use) the largest zone in a calculation.

I have played with the use of a complex IIF Statement, but I am having a hard time getting it to show until all fields are filled (will not always be the case).

I am not really a "code" user, but I think this is something that would benefit from code.

 

[dmyoungsal]

I have 4 unbound fields that I use to enter Zones (range of Miles). the fields are cboZone1, cboZone2, cboZone4 and cboZone4.

Most times the 2-4 fields will be blank and it is highly possible the zones will not be entered in any particular order. They will most likely be entered lowest first, largest last.

I need to find (and use) the largest zone in a calculation.

I have played with the use of a complex IIF Statement, but I am having a hard time getting it to show until all fields are filled (will not always be the case).

I am not really a "code" user, but I think this is something that would benefit from code.

I should add, I got this working really well in Excel (using the nested IF statement), but after pasting it in tot the text field and editing the field names, etc, it is not working like it should.

 

[pbaldy]

One way:

https://support.microsoft.com/en-us/kb/209857

You wouldn't have been able to use the Excel formula, as the structure is different than Access.

 

[dmyoungsal]

I am back....

I posted last week that I was able to get my form to find the largest number from a group of fields. I did this using nested IIF's. But yesterday I got a curve thrown at me to add two more fields and after editing the statement, I am having difficulty getting the format to work. (I don't know for sure, but I may have exceeded the length of the nested iif statement.

This is what I am working with:
I have six fields (AvgMPH1 > AvgMph6). If all fields are complete, I need to find and use the largest number in another calc).

I have read something about a DMAX function, but I do not know how to implement it.

Your help is appreciated.

 

[Uncle Gizmo]

When you say a group of Fields do you mean text boxes on a form?

Sent from my SM-G925F using Tapatalk

 

[dmyoungsal]

When you say a group of Fields do you mean text boxes on a form?

Sent from my SM-G925F using Tapatalk

yessir.....

 

[Uncle Gizmo]

Oh I see, it's four combo boxes...

Sent from my SM-G925F using Tapatalk

 

[jdraw]

I have read something about a DMAX function, but I do not know how to implement it.

Google to the rescue... DMax

 

[plog]

Dmax only works on data in a table/query. You said your data is in an unbound form inputs. You didn't say where your nested Iif statements are at. Are they in the control source of another input? Did you write a custom function?

In either case, my advice is to use a custom function now. The function would look something like this (not actual code, psuedo code):

Function get_Largest() As Double 

ret=0
' return value, this is what the function will return

if Input1>ret then ret=Input1
if Input2>ret then ret=Input2
if Input3>ret then ret=Input3
....
if InputLast>ret then ret=InputLast 
' logic to get largest value of all inputs
' bonus points if you use a loop instead of a ton of if statements

get_Largest=ret 

End Function

 

[dmyoungsal]

Dmax only works on data in a table/query. You said your data is in an unbound form inputs. You didn't say where your nested Iif statements are at. Are they in the control source of another input? Did you write a custom function?

In either case, my advice is to use a custom function now. The function would look something like this (not actual code, psuedo code):

Function get_Largest() As Double 

ret=0
' return value, this is what the function will return

if Input1>ret then ret=Input1
if Input2>ret then ret=Input2
if Input3>ret then ret=Input3
....
if InputLast>ret then ret=InputLast 
' logic to get largest value of all inputs
' bonus points if you use a loop instead of a ton of if statements

get_Largest=ret 

End Function

All this is contained in a hidden field on my form.

I am not well versed in writing code

 

[Uncle Gizmo]

The function below will interrogate any number of combo-boxes (You must place an "x" in the tag property of the ones you want to look at) and return the value of the maximum combo box.

Call it like this:-

MsgBox " >>> Max Value >>> " & fGetMaxValueFromSelectedCombos

Private Function fGetMaxValueFromSelectedCombos() As Long

Dim lngMaxValue As Long
Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
            Case acComboBox ', acCheckBox, acLabel, acListBox, acOptionButton, acOptionGroup, acTextBox, acToggleButton
                If Ctrl.Tag = "x" Then
                    If Ctrl.Value > lngMaxValue Then lngMaxValue = Ctrl.Value
                End If
        End Select
    Next Ctrl

fGetMaxValueFromSelectedCombos = lngMaxValue
    
End Function      'fGetMaxValueFromSelectedCombos

 

[dmyoungsal]

The function below will interrogate any number of combo-boxes (You must place an "x" in the tag property of the ones you want to look at) and return the value of the maximum combo box.

Call it like this:-

MsgBox " >>> Max Value >>> " & fGetMaxValueFromSelectedCombos

Private Function fGetMaxValueFromSelectedCombos() As Long

Dim lngMaxValue As Long
Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
            Case acComboBox ', acCheckBox, acLabel, acListBox, acOptionButton, acOptionGroup, acTextBox, acToggleButton
                If Ctrl.Tag = "x" Then
                    If Ctrl.Value > lngMaxValue Then lngMaxValue = Ctrl.Value
                End If
        End Select
    Next Ctrl

fGetMaxValueFromSelectedCombos = lngMaxValue
    
End Function      'fGetMaxValueFromSelectedCombos

Not sure if this matters, but the fields I am looking at are not combo boxes

 

[Uncle Gizmo]

fields are cboZone1, cboZone2, cboZone4

They sure look like combo boxes!



Sent from my SM-G925F using Tapatalk

 

[plog]

They sure look like combo boxes!

My grandmother's cat named 'dog' just might make your head explode then.

 

[Uncle Gizmo]

In this line:-

Case acComboBox

Replace "acComboBox" with the type of control you want to interrogate...

And put "x" in the tag properties of those controls.

Sent from my SM-G925F using Tapatalk

 

[dmyoungsal]

They sure look like combo boxes!



Sent from my SM-G925F using Tapatalk

I am sorry, I missed your comment regarding Combo boxes.

No, they are text boxes (which are filled when a user selects from a combo box). But these boxes are pure text boxes.

 

[dmyoungsal]

I am sorry.. none of this is making sense.....

I know I should be able to understand this, but I am not.

 

[Uncle Gizmo]

Don't worry about not understanding it, it takes months, if not years to understand it.

You are having a go, and that's the main thing.

It appears to me that you have the code in a text box, and that's a natural way to progress.

However, to take it further you need to think "event driven".

All that means is when something happens some code is run.

The simplest way to get started with event driven code is to add a command button, pressing the command button is the event, and it's called the on click event.

Add a command button to your form, open it's property sheet, find events, find the on click event, press on the ellipsis (3 dots ...) Choose code, (Not Macros)

It should automatically create a code stub for you.

Place the code just below
Call it like this:-

Between the code stub lines.

Also paste the function in above or below the button on click code.

If you get stuck let us know...

 

[dmyoungsal]

Dmax only works on data in a table/query. You said your data is in an unbound form inputs. You didn't say where your nested Iif statements are at. Are they in the control source of another input? Did you write a custom function?

In either case, my advice is to use a custom function now. The function would look something like this (not actual code, psuedo code):

Function get_Largest() As Double 

ret=0
' return value, this is what the function will return

if Input1>ret then ret=Input1
if Input2>ret then ret=Input2
if Input3>ret then ret=Input3
....
if InputLast>ret then ret=InputLast 
' logic to get largest value of all inputs
' bonus points if you use a loop instead of a ton of if statements

get_Largest=ret 

End Function

This is the code I went with... Straight forward.. thank you

 

[gemma-the-husky]

Why are the boxes unbound?

Why should you need to select a value based on unbound boxes.