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

theYaniac

New Member
Joined
Jan 7, 2018
Messages
36
Office Version
365
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
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,847
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
36
Office Version
365
Platform
Windows
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
820
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
36
Office Version
365
Platform
Windows
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
820
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,847
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
36
Office Version
365
Platform
Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,102,187
Messages
5,485,255
Members
407,490
Latest member
leogaleleo84

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top