Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15

    Finding the maximum value in Textboxes and then show the result in a text in another textbox

    Hi everyone!
    I have 4 textboxes which are: txt_1, txt_2, txt_3 and txt_4
    I'm trying to find the maximum value in 4 textboxes by clicking the combine button and then based on where the maximum value is whether in txt_1, txt_2, txt_3 or txt_4, a "text" will appear in another textbox. That "text" is txt_1 = "Na2CO3", txt_2 = "HCL", txt_3 = "HO3" and txt_4 = "Plain".

    Click image for larger version. 

Name:	Inkeduse_LI.jpg 
Views:	14 
Size:	37.5 KB 
ID:	27995

    Private Sub Command106_Click()
    Dim Max As Integer
    If (txt_1 > txt_2) And (txt_1 > txt_3) And (txt_1 > txt_4) Then
    Max = txt_1
    ElseIf (txt_2 > txt_1) And (txt_2 > txt_3) And (txt_2 > txt_4) Then
    Max = txt_2
    ElseIf (txt_3 > txt_1) And (txt_3 > txt_2) And (txt_3 > txt_4) Then
    Max = txt_3
    Else
    Max = txt_4
    End If
    Text104 = Max 'Text104 is the textbox on the right



    End Sub

    This is the code i'm working on(On Click event) and I'm currently stuck here
    If the maximum value is equal let say 3 values are the same then the preference is from plain>hcl>h03>na2co3

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This is one way...
    Code:
    Private Sub Command106_Click()
    Dim sMax As String
    If (txt_1 > txt_2) And (txt_1 > txt_3) And (txt_1 > txt_4) Then
        sMax = "Na2CO3"
    ElseIf (txt_2 > txt_1) And (txt_2 > txt_3) And (txt_2 > txt_4) Then
        sMax = "HCL"
    ElseIf (txt_3 > txt_1) And (txt_3 > txt_2) And (txt_3 > txt_4) Then
        sMax = "HO3"
    Else
        sMax = "Plain"
    End If
    Me.Text42.Visible = True
    Text42=sMax
    End Sub
    

  3. #3
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Thanks it works! But there is another problem, what if the value is equal? If the maximum value is equal let say 3 values are the same then the preference is from plain>hcl>h03>na2co3 how do i do that?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Offhand I don't see a clever way to do that. All I can see is a confusing hive of If-Then-Else. Maybe someone else has an idea using an array, container or temp table.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't get this
    the preference is from plain>hcl>h03>na2co3
    How can there be a descending order of values if they're the same? This is what I came up with to find the max (which would be much easier to expand if need be). It works with the values 1,2,3,4 - which ought to work if you use the control names instead AND they hold numbers and not text.
    Code:
    Public Function MaxInArray(varArray As Variant) As Variant
    Dim varMax As Variant
    
    varMax = varArray(UBound(varArray))
    MsgBox varMax
    
    End Function
    Code:
    Private Sub BuildArray()
    
    MaxInArray Array(1, 2, 3, 4)
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Example if the total are the same plain>hcl>h03>na2co3:
    Scenario 1 - the total below Na2CO3 - 0 HCl - 3 HO3 - 3 Plain - 3 Then I want the result to be "Plain" in the textbox(Text104) on the right
    Scenario 2 - the total below Na2CO3 - 4 HCl - 4 HO3 - 2 Plain - 1 Then I want the result to be "HCl" in the textbox(Text104) on the right
    Scenario 3 - the total below Na2CO3 - 3 HCl - 0 HO3 - 3 Plain - 1 Then I want the result to be "HO3" in the textbox(Text104) on the right

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    OK been playing around...

    This sub will work from the button

    Code:
    Private Sub Command106_Click()
    Dim myarray(4) As Integer
    myarray(1) = Me.txt_1
    myarray(2) = Me.txt_2
    myarray(3) = Me.txt_3
    myarray(4) = Me.txt_4
    msgbox  Mid(arranging(myarray), 2)
    DoCmd.DeleteObject acTable, "tmptable"
    End Sub
    This function (put in a module) will return the value you need

    Code:
    Function arranging(numbers As Variant) As String
    Dim txt As TextBox
    Dim mystring As String
    DoCmd.SetWarnings (WarningsOff)
    
    mystring = "CREATE TABLE tmptable (col1 INTEGER ," _
    & "txtname CHAR(25)," _
    & "TotalNum integer )"
    
    DoCmd.RunSQL mystring
    For i = 1 To 4
    Set txt = Forms("form1").Controls("txt_" & i)
    myval = CStr(txt.Tag)
    mystring = "insert into tmptable(txtname,TotalNum) Values(" & Chr(34) & myval & Chr(34) & "," & numbers(i) & ");"
    
    DoCmd.RunSQL mystring
    Next i
    mystring = "SELECT top 1 tmptable.TotalNum, tmptable.txtname" _
    & " FROM tmptable" _
    & " ORDER BY tmptable.TotalNum desc, tmptable.txtname;"
    
    Set rst = CurrentDb.OpenRecordset(mystring)
       ' new code:
       arranging = rst!txtname
       rst.Close
       Set rst = Nothing
    DoCmd.SetWarnings (WarningsOn)
    End Function
    To prepare, add the following tags to your 4 textboxes

    1plain
    2hcl
    3ho3
    4na2co3

    And thanks

    Loved it!!

  8. #8
    adeebarsad is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Almost there! Why do I get a msgbox? Also after that an error came up "Table "tmpTable" already exists"

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    might be a simpler way; working on it

    you get a message box because it's called for right after myarray(4)
    Don't forget - arrays are zero based so tweaking this code down the road might introduce errors.

  10. #10
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Finding the maximum value in Textboxes and then show the result in a text in another textbox

    Swap msgbox mid( and so on

    For

    text104 = mid( and so on




    Sent from my iPhone using Tapatalk

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So here's a solution using code from here http://allenbrowne.com/func-09.html which may prove useful when getting the max or min values from a list. Any of my attempts to condense the code by creating a variable to pass the same list to the MaxOfList function as well as the InstrRev function was not successful, so 2 variables hold similar values. The finished code will be much smaller when all the unnecessary stuff is removed. The logic is
    - get the max value of the array
    - start from the right of the array values and find the right-most position of the max value.
    - the Select Case block chooses the value for Text104 based on the position of the max value
    Based on your posts, I understand that whenever a max value is repeated, its right-most position determines what goes in Text104.

    Code:
    Private Sub BuildArray()
    Dim maxValue As Long
    Dim strValues As String
    
    '*****this block should not be required as the form controls can be referenced to get the values
    'I needed it because I don't have the form or its controls
    'Call to MaxOfList should be changed to refer to the form controls and not txt1, txt2, etc.
    
    Dim txt1 As Long, txt2 As Long, txt3 As Long, txt4 As Long
    'comment out all but one line for testing; the desired result to be passed to Text104
    'txt1 = 1: txt2 = 2: txt3 = 3: txt4 = 4 'Na2CO3, HCl, HO3, Plain => Plain
    'txt1 = 0: txt2 = 3: txt3 = 3: txt4 = 3 ' => Plain
    'txt1 = 4: txt2 = 4: txt3 = 2: txt4 = 1 ' => HCl
    txt1 = 3: txt2 = 0: txt3 = 3: txt4 = 1 ' => HO3
    '******
    
    'get the max value in the array
    maxValue = MaxOfList(txt1, txt2, txt3, txt4)
    strValues = txt1 & txt2 & txt3 & txt4
    
    'find the right-most position of the max value and pass to a Select Case block
    Select Case InStrRev(strValues, maxValue, -1)
        Case Is = 4
            MsgBox "Plain"
        Case Is = 3
            MsgBox "HO3"
        Case Is = 2
            MsgBox "HCl"
        Case Else
            MsgBox "Na2CO3"
    End Select
    
    End Sub
    Code:
    Function MaxOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.
    
    varMax = Null           'Initialize to null
    For i = LBound(varValues) To UBound(varValues)
     If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
        If varMax >= varValues(i) Then
           'do nothing
        Else
           varMax = varValues(i)
        End If
     End If
    Next
    
    MaxOfList = varMax
    End Function
    EDIT:
    I see that I omitted part of the function. As long as I'm fixing my cut and paste screw-up, I thought I'd post what the short version of the sub should look like (where the form control values are assigned to variables).

    Code:
    Private Sub BuildArray()
    Dim maxValue As Long
    Dim strValues As String
    txt1 = Me.Textbotx1
    txt2 = Me.Textbotx2
    txt3 = Me.Textbotx3
    txt4 = Me.Textbotx4
    
    maxValue = MaxOfList(txt1, txt2, txt3, txt4)
    strValues = txt1 & txt2 & txt3 & txt4
    Select Case InStrRev(strValues, maxValue, -1)
        Case Is = 4
            MsgBox "Plain"
        Case Is = 3
            MsgBox "HO3"
        Case Is = 2
            MsgBox "HCl"
        Case Else
            MsgBox "Na2CO3"
    End Select
    
    End Sub
    Last edited by Micron; 03-25-2017 at 05:44 PM. Reason: incomplete code
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's another way:
    Code:
    Private Sub cmdCalc_Click()
        Dim P1 As String
        Select Case fcnGreaterEqual("txt_4")
            Case Is = True
                P1 = "Plain"
                GoTo ShowResult
            Case Else
                P1 = vbNullString
        End Select
        Select Case fcnGreaterEqual("txt_3")
            Case Is = True
                P1 = "HO3"
                GoTo ShowResult
            Case Else
                P1 = vbNullString
        End Select
        Select Case fcnGreaterEqual("txt_2")
            Case Is = True
                P1 = "HCL"
                GoTo ShowResult
            Case Else
                P1 = vbNullString
        End Select
        Select Case fcnGreaterEqual("txt_1")
            Case Is = True
                P1 = "Na2CO3"
                GoTo ShowResult
            Case Else
                P1 = vbNullString
        End Select
    ShowResult:
        Debug.Print P1
        text104= P1
    End Sub
    Private Function fcnGreaterEqual(arg As String) As Boolean
        fcnGreaterEqual = False
        Select Case arg
            Case "txt_4"
                If txt_4 >= txt_3 And _
                   txt_4 >= Txt_2 And _
                   txt_4 >= txt_1 _
                   Then fcnGreaterEqual = True
            Case "txt_3"
                If txt_3 >= txt_4 And _
                   txt_3 >= Txt_2 And _
                   txt_3 >= txt_1 _
                  Then fcnGreaterEqual = True
            Case "txt_2"
                If Txt_2 >= txt_4 And _
                   Txt_2 >= txt_3 And _
                   Txt_2 >= txt_1 _
                   Then fcnGreaterEqual = True
            Case "txt_1"
                If txt_1 >= txt_4 And _
                   txt_1 >= txt_3 And _
                   txt_1 >= Txt_2 _
                   Then fcnGreaterEqual = True
        End Select
    End Function

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Looking at your code; have to say I don't get how it can work.
    IF
    txt1 = 3:
    txt2 = 0:
    txt3 = 3:
    txt4 = 1 (which means Text104 should end up being HO3 according to post #6)
    Evaluating the expressions one at a time:
    Case "txt_4"
    If txt4 >= txt3 And _ (1>=3 is False, so the rest of the expression is redundant. Function returns False)

    Case "txt_3"
    If txt3 >= txt4 And _ (3>=1 is True)
    txt3 >= txt2 And _ (3>=0 is True)
    txt3 >= txt1 _ (3>=3 is True (function returns True)

    Case "txt_2"
    If Txt_2 >= txt_4 And _ (0 >=1 is False so the rest of the expression is redundant. Function returns False)
    Txt_2 >= txt_3 And _
    Txt_2 >= txt_1 _

    Case "txt_1"
    If txt_1 >= txt_4 And _ (3>=1 is True)
    txt_1 >= txt_3 And _ (3>=3 is True)
    txt_1 >= Txt_2 _ (3>=0 is True) (function returns True)

    The function returns true for case 3 and 1. Now the sub evaluates 1 last, so P1 (thus the control) becomes "Na2CO3"
    The desired result was HO3, not Na2CO3

    Sorry if I'm wrong and didn't follow the logic correctly.
    Last edited by Micron; 03-25-2017 at 06:36 PM. Reason: spelin
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Sorry if I'm wrong and didn't follow the logic correctly.
    If txt4 >= txt3 And _ (1>=3 is False, so the rest of the expression is redundant. Function returns False)
    The rest of the expression is evaluated because of the "AND"

    I tested it in an actual form that I created. It returns the correct result for all the examples.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well if it works then I can't argue that except to stick with the logic that the and part should be redundant since the first part is not true.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 01-08-2015, 12:35 PM
  2. Text box show result of query
    By Ash in forum Access
    Replies: 2
    Last Post: 08-06-2014, 03:00 PM
  3. Replies: 4
    Last Post: 04-03-2014, 08:03 AM
  4. show a query result in a text box
    By mikael in forum Access
    Replies: 2
    Last Post: 09-13-2012, 02:17 AM
  5. Replies: 1
    Last Post: 09-28-2011, 09:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums