Results 1 to 10 of 10

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

  1. #1
    New Member
    Join Date
    Jan 2018
    Location
    Michigan
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    649
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,698
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  4. #4
    New Member
    Join Date
    Jan 2018
    Location
    Michigan
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    649
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

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

    Just copy the code and change the logic.?
    Office 2007
    Access novice. Sometimes trying to give something back

  6. #6
    New Member
    Join Date
    Jan 2018
    Location
    Michigan
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by theYaniac; Jun 17th, 2019 at 08:13 AM. Reason: additional information

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    649
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by theYaniac View Post
    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.
    Office 2007
    Access novice. Sometimes trying to give something back

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    446
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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 by stumac; Jun 17th, 2019 at 09:04 AM.

  9. #9
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,698
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  10. #10
    New Member
    Join Date
    Jan 2018
    Location
    Michigan
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •