Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Minimun function in MS Access

Posted on 2013-05-14
Medium Priority
259 Views
I have 3 fields that have either a 0 or is null or have  a value ~ How can select the lowest value among the 3 fields in MS Access query?  example:
Field 1       Field 2          Field 3       Minimun value would be:
0                                        35.75                35.75
15.01          14.39                    0                 14.39
9.56              7.59                6.59                6.59
0
Question by:deefel
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 77

Expert Comment

ID: 39167258
Do you regard Null and 0 as the same or what?
0

Author Comment

ID: 39167322
no they are separate. I would evaluate for either a Null value or a 0 value
0

LVL 77

Expert Comment

ID: 39167336
So if they are to treated the same ,  do want null to be greater than 0 or less than 0?
0

Author Comment

ID: 39167375
greater than 0
0

LVL 93

Accepted Solution

Patrick Matthews earned 400 total points
ID: 39168136
1) Add this UDF to your VBA project, from my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1775-Computing-row-wise-aggregations-in-Access.html

``````Function RowStats(Stat As String, ParamArray Vars())

' Function by Patrick Matthews

' This code may be used and distributed freely, so long as you attribute authorship, and indicate
' what URL you found the code at

' This Access UDF calculates various stats for the values passed into the ParamArray.  It
' was originally designed for passing several values from a particular row set for
' evaluation; since the values come from the same row, the usual aggregate functions would
' not be appropriate.

' The Stat argument (NOT case sensitive) determines what statistic is calculated:
' "count":          count of non-null values
' "min", "max":     minimum or maximum
' "sum", "avg":     sum or average, excluding strings
' "var", "stdev":   sample variance or standard deviation (excluding strings)
' "varp", "stdevp": population variance or standard deviation (excluding strings)

' You may pass any value in the ParamArray.  Strings, nulls, and dates are ignored for the
' sum, avg, stdev, stdevp, var, and varp calculations.  (Thus, you could use this function to
' add several columns together without wrapping each column with Nz() to handle nulls...)

' If run from VBA, this function appears to accept any number of values in the Vars argument;
' I successfully tested passing several hundred values.  If run from the Access query editor,
' the limit appears to be about 28 values.  If you need to process more than 28 values, you
' should use the related function RowStatsFieldList.  (You can overcome this limit by embedding
' RowStats expressions, but this will produce potentially erroneous results for the Avg,
' StDev[P], or Var[P] stats

' Please note that you can pass arrays as elements of the Vars ParamArray; indeed, the
' RowStatsFieldList function does that

' As with the regular aggregate functions, if all of the parameters are null, then the
' return value is null, except on count, where the return would be zero

Dim Numerator As Double
Dim Denominator As Double
Dim Counter As Long, Counter2 As Long
Dim Result As Variant
Dim Mean As Double

' Force to upper case to make sure string comparisons are always performed as expected;
' Access uses Option Compare Database by default, but other VBA/VB6 uses binary default

Stat = UCase(Stat)

Select Case Stat

' In each Case below, loop through the elements of the Vars ParamArray.  If the element
' is itself an array, then loop through its elements

Case "COUNT"

' Increment the result for each non-null value in the array

Result = CLng(0)

For Counter = LBound(Vars) To UBound(Vars)
If Not IsArray(Vars(Counter)) Then
If Not IsNull(Vars(Counter)) Then Result = Result + 1
Else
For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
If Not IsNull(Vars(Counter)(Counter2)) Then Result = Result + 1
Next
End If
Next

Case "MIN"

' Initialize the result to Null, then check all non-Null values in turn to see
' if it is less.

Result = Null

For Counter = LBound(Vars) To UBound(Vars)
If Not IsArray(Vars(Counter)) Then
If IsNull(Result) And Not IsNull(Vars(Counter)) Then
Result = Vars(Counter)
ElseIf Vars(Counter) < Result Then
Result = Vars(Counter)
End If
Else
For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then
Result = Vars(Counter)(Counter2)
ElseIf Vars(Counter)(Counter2) < Result Then
Result = Vars(Counter)(Counter2)
End If
Next
End If
Next

Case "MAX"

' Initialize the result to Null, then check all non-Null values in turn to see
' if it is greater.

Result = Null

For Counter = LBound(Vars) To UBound(Vars)
If Not IsArray(Vars(Counter)) Then
If IsNull(Result) And Not IsNull(Vars(Counter)) Then
Result = Vars(Counter)
ElseIf Vars(Counter) > Result Then
Result = Vars(Counter)
End If
Else
For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then
Result = Vars(Counter)(Counter2)
ElseIf Vars(Counter)(Counter2) > Result Then
Result = Vars(Counter)(Counter2)
End If
Next
End If
Next

Case "AVG", "SUM"

' Check each value in turn.  If it is numeric, then increment numerator and denominator.
' Divide numerator by denominator to get an average, or by 1 to get the sum.  Any Date
' values are coerced into Double

For Counter = LBound(Vars) To UBound(Vars)
If Not IsArray(Vars(Counter)) Then
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
ElseIf IsDate(Vars(Counter)) Then
Numerator = Numerator + CDbl(Vars(Counter))
Denominator = Denominator + 1
End If
Else
For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
If IsNumeric(Vars(Counter)(Counter2)) Then
Numerator = Numerator + Vars(Counter)(Counter2)
Denominator = Denominator + 1
ElseIf IsDate(Vars(Counter)(Counter2)) Then
Numerator = Numerator + CDbl(Vars(Counter)(Counter2))
Denominator = Denominator + 1
End If
Next
End If
Next

If Denominator > 0 Then
Result = Numerator / IIf(Stat = "AVG", Denominator, 1)
Else
Result = Null
End If

Case "STDEV", "STDEVP", "VAR", "VARP"

' Take one pass through the set to determine the average, and then determine the
' sum of squared deviances from the mean.  Divide by number of elements in the
' array for population or (elements - 1) for sample.  If standard deviation,
' take square root.  Any Date values are coerced into Double

' This pass generates the numerator and denominator needed for the average

For Counter = LBound(Vars) To UBound(Vars)
If Not IsArray(Vars(Counter)) Then
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
ElseIf IsDate(Vars(Counter)) Then
Numerator = Numerator + CDbl(Vars(Counter))
Denominator = Denominator + 1
End If
Else
For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
If IsNumeric(Vars(Counter)(Counter2)) Or IsDate(Vars(Counter)(Counter2)) Then
Numerator = Numerator + Vars(Counter)(Counter2)
Denominator = Denominator + 1
ElseIf IsDate(Vars(Counter)(Counter2)) Then
Numerator = Numerator + CDbl(Vars(Counter)(Counter2))
Denominator = Denominator + 1
End If
Next
End If
Next

' Make sure there are enough numeric elements to avoid a division by zero error.  If not,
' return Null

If (Stat Like "*P" And Denominator > 0) Or (Not Stat Like "*P" And Denominator > 1) Then

Mean = Numerator / Denominator

' This pass sums the squares of the differences between each data point and the mean

For Counter = LBound(Vars) To UBound(Vars)
If Not IsArray(Vars(0)) Then
If IsNumeric(Vars(Counter)) Then
Result = Result + (Vars(Counter) - Mean) ^ 2
ElseIf IsDate(Vars(Counter)) Then
Result = Result + (CDbl(Vars(Counter)) - Mean) ^ 2
End If
Else
For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
If IsNumeric(Vars(Counter)(Counter2)) Then
Result = Result + (Vars(Counter)(Counter2) - Mean) ^ 2
ElseIf IsDate(Vars(Counter)(Counter2)) Then
Result = Result + (CDbl(Vars(Counter)(Counter2)) - Mean) ^ 2
End If
Next
End If
Next

' Divide by N for population, and N-1 for sample

If Stat Like "*P" Then
Result = Result / Denominator
Else
Result = Result / (Denominator - 1)
End If

' Take square root if standard deviation

If Stat Like "S*" Then Result = Result ^ 0.5

Else
Result = Null
End If

Case Else

' If Stat is none of the above, then return Null -- invalid Stat

Result = Null
End Select

' Set return value

If Not IsNull(Result) Then RowStats = Result Else RowStats = Null

End Function
``````

2) Use a query like this:

``````SELECT Field1, Field2, Field3,
RowStats("min", IIf(Field1 <> 0, Field1, Null), IIf(Field2 <> 0, Field2, Null), IIf(Field3 <> 0, Field3, Null)) AS RowMin
FROM SomeTable
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll