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

[theYaniac]

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]

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]

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

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]

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]

That function will give you the Max value, You were looking for the Min value.?

Just copy the code and change the logic.?

 

[theYaniac]

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

 

[welshgasman]

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]

Here is a function to get the min value, enter it in a module:

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:

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

 

[Micron]

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]

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.