DMin function on an Access 2016 form to find lowest value in multiple controls

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
I am very new to Access and I am trying to figure out of I can use the DMin function to return the lowest value found in several text boxes at once. I am building a simple database for tracking stock and option trades. The tracker form will be filled out manually for scalable entries into positions, ex. Contract entry 1 $1.08, Contract Entry 2 $1.27, Contract Entry 3 is $1.47. I would also like to have a text box on the form that would auto populate based on the lowest contract price found in one of the 3 text boxes with the contract prices. Not sure if this can be done with the expression builder or if I will need to do this with VBA. Any suggestions would be greatly appreciated.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
678
If the database was normalised, each of those would be a separate record, so DMin would work.

Best I can think of is to just compare (as there are only 3).
Compare 1 & 2 to get a variable set, then compare 3 with that variable.?

HTH
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
You will never have a 4th (or more) contract price for an item? If maybe, your design isn't correct (may not be correct even if the answer is 'never').

Since you are using currency values, something like (use something other than a button click event if you'd rather).
Private Sub Command17_Click()
MaxValue
End Sub

Code:
Function MaxValue() As Currency

MaxValue = Nz(Me.Text8, 0)
If Me.Text10 > MaxValue Then MaxValue = Me.Text10
If Me.Text13 > MaxValue Then MaxValue = Me.Text13

Me.Text15 = MaxValue

End Function
If there were many more than just 3, there are other ways. Also, if your fields are not currency, won't work as written, and it is pretty basic as is. Obviously, use your own control names.

You could investigate the Switch function for use in the target textbox, but I think it could get ugly in a hurry.
 

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
I couldn't get the Function to work, I tried it a an OnEnter Event as well as an OnChange Event. As far as the database set goes, not sure if I have it set up incorrectly or not. I am unable to attach a screen shot of the form.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
678
That function will give you the Max value, You were looking for the Min value.?

Just copy the code and change the logic.?
 

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
Should this be set as a private function for the text box or a public function for the entire database? With it set as a public function, I get a Name error in the text box
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
678
Should this be set as a private function for the text box or a public function for the entire database? With it set as a public function, I get a Name error in the text box
If you just want it in that form, I put it in that form as written.
If you might use it elsewhere, put it in a module as Public.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
Here is a function to get the min value, enter it in a module:

Code:
Function GetMin(ParamArray Numbers()) As Variant
If UBound(Numbers) < 0 Then Exit Function
Dim x As Variant
GetMin = Numbers(0)
For Each x In Numbers
    GetMin = IIf(x < GetMin, x, GetMin)
Next
End Function
You can then call it in a text box expression like:
Code:
=GetMin([text1],[text2],[text3])
where the text1 etc are replaced by the name of your text boxes.

This will mean you don't need to directly call the code from the current and update events, if also means you can use it in queries/reports and can use more or fewer parameters.
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
Boy, a lot can happen here overnight! Sorry for the Max thing - got my wires crossed from a post on another forum. Some things worth noting, I think:
For future posts, "didn't work" never helps much. What you tried does.
you cannot have Me in a public function outside of a form or report, so that would not work as written either.
you probably don't realize how the OnChange event works (it's called after every keystroke in the control)

You are probably OK now?
 

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
thank you for the replies and I apologize for the delay in response. I have been tied up with work. I was able to get the function working as you described. I appreciate the help, thanks a bunch.:biggrin:
 

Forum statistics

Threads
1,078,275
Messages
5,339,195
Members
399,288
Latest member
ossa

Some videos you may like

This Week's Hot Topics

Top