Results 1 to 15 of 15
  1. #1
    cdixon102419607 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    2

    Is this Code Correct?

    Hi,

    I want to make sure that my MS Access VBA code is correct. Lets say that I have three fields in a table containing three values:

    Val1 = 8 (for Field1)
    Val2 = 7 (for Field2)
    Val3 = 5 (for Field3)



    If I am using the following function to get the max value out of the three values which is 8 of course, am I correct in my logic?


    Public Function fnMax2(Val1 As Double, Val2 As Double, Val3 As Double)

    If Val1 > Val2 Or Val1 > Val3 Then
    fnMax2 = Val1
    ElseIf Val2 > Val1 Or Val2 > Val3 Then
    fnMax2 = Val2
    Else
    fnMax2 = Val3
    End If

    End Function

    Thanks for any assistance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd just use this, as it's more flexible:

    Minimum or Maximum Value Across Fields of Record

    And no, I don't believe your logic would work. What if val1 was less than val2 but greater than val3?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Changing the OR's to AND's should work:

    Code:
    Public Function fnMax2(Val1 As Double, Val2 As Double, Val3 As Double)
    
     If Val1 > Val2 AND Val1 > Val3 Then
       fnMax2 = Val1
    ElseIf Val2 > Val1 AND Val2 > Val3 Then
          fnMax2 = Val2
    Else
          fnMax2 = Val3
    End If
      
    End Function

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you test the code before asking here?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I tried the code modification suggested by John_G using:
    Code:
    ? fnmax2(8,8,4)
    It returned 4...


    My version:
    Code:
    Public Function fnMax3(Val1 As Double, Val2 As Double, Val3 As Double) As Double
        Dim dMaxVal As Double
    
        dMaxVal = Val1
    
        If Val2 > dMaxVal Then
            dMaxVal = Val2
        End If
    
        If Val3 > dMaxVal Then
            dMaxVal = Val3
        End If
    
        fnMax3 = dMaxVal 
    End Function

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why reinvent the wheel? Try the code suggested by pbaldy.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Steve -

    The line in your version:
    fnMax3 = dMaxVal

    is not in an If-then block - it always executes, thus always returning the value of the last parameter!

    That said, I see the error in mine; the ">" have to be ">=":

    Code:
    Public Function fnMax2(Val1 As Double, Val2 As Double, Val3 As Double)
    
    If Val1 >= Val2 AND Val1 >= Val3 Then
       fnMax2 = Val1
    ElseIf Val2 >= Val1 AND Val2 >= Val3 Then
          fnMax2 = Val2
    Else
          fnMax2 = Val3
    End If
      
    End Function
    A few swats with a wet noodle for both of us?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    mmmmmm... noodles. lunch time...!!

    @John

    fnMax3 = dMaxVal is to set the return value (equal to the value of dMaxVal - not one of the parameters).
    And dMaxVal is the largest of the 3 numbers. Doesn't need to be in an IF-Then block...

    You made me rethink the code
    Code:
    Public Function fnMax3(Val1 As Double, Val2 As Double, Val3 As Double) As Double
    
        fnMax3 = Val1          'default return value
    
        If Val2 > fnMax3 Then  'compare Val2 to fnMax3
            fnMax3 = Val2
        End If
    
        If Val3 > fnMax3 Then  'compare Val3 to fnMax3
            fnMax3 = Val3
        End If
    
    End Function

    Meatballs for all!!

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Sigh....

    Sometimes it just doesn't pay to get up in the morning, and leave brain in bed!

    Thanks

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What is really bad is when I get up on the wrong side of the bed...... the wall side!!

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The code at the link seems a bit lengthy unless you need it for a query, I guess. Otherwise, as long as you won't ever have more than 3 values the code solution would work, but I'd hate to see how convoluted it would get if a 4th value was introduced. Here's something I wrote that seems to work with lists of different lengths & should with any number type I think.
    Code:
    Function GetMaxVal(ParamArray aryValues() As Variant) As Variant
    Dim i As Variant, varCurMax As Variant
    
    varCurMax = aryValues(0)
    For i = 1 To UBound(aryValues)
    If varCurMax < aryValues(i) Then varCurMax = aryValues(i)
    Next
    GetMaxVal = varCurMax
    
    End Function
    ?getmaxval (21,2,3,7,6,5,11) = 21
    ?getmaxval (2,3,7,6,5,1) = 7
    ?getmaxval (1,2,3,7,6,5,11) = 11
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In retrospect, i could/should be an integer.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    The code at the link seems a bit lengthy
    I'm curious why you feel that way. If I take out the comments, switch the If/Then to single line format and put the dims on one line, it's exactly the same as yours. All of those things are good for kb code, as they probably help newbies.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Because I was considering all of it, i.e. without segregating the functions
    Can I at least get points for writing something that will take more than 3 values?

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    A minute with google:

    http://allenbrowne.com/func-09.html


    OK. I see the same solution (only in fresher presentation) was suggested earlier by pbaldy

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with VBA code to correct bad data
    By Processor in forum Programming
    Replies: 3
    Last Post: 07-09-2017, 07:01 PM
  2. IS this code correct?
    By Jen0dorf in forum Queries
    Replies: 1
    Last Post: 10-24-2015, 04:23 PM
  3. Replies: 5
    Last Post: 10-13-2015, 02:53 PM
  4. code to determine correct dimensions
    By mbar in forum Programming
    Replies: 3
    Last Post: 05-03-2012, 04:27 PM
  5. Correct code for challenging dates?
    By thekruser in forum Queries
    Replies: 6
    Last Post: 11-08-2010, 03:38 PM

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