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.

 

[dmyoungsal]

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

Sent from my SM-G925F using Tapatalk

yessir.....

 

[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

 

[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

 

[plog]

They sure look like combo boxes!

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

 

[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.

 

[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.

 

[dmyoungsal]

Why are the boxes unbound?

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

The original purpose of the project was to create a form that would help our sales people create transportation rate quotes that would be printed and not need to be referenced again (only looking at the printed page (which is attached to the contract)). Thus the whole form was created using unbound fields.

After getting all this to work and showing the team, the project changed and they wanted to be able to pull up the quotes later.