SQL advanced -> UDF Min/Max value function in MS-Access SQL

In many cases it is very convinient to use User Defined Function in SQL query. For example, if we need to chose Minimal or Maximal value between two columns. In our example, we want to chose Maximal sales total beween Q1 and Q2. Here is the functions definition that will be stored in VBA Module of Access .mdb file:

Function fn_MinVal(ByVal Num1 As Double, ByVal Num2 As Double) As Double
' this VBA function returns minimal value
  Dim MinValue As Double

On Error GoTo Err_SuperTrap

If (Num1 <= Num2) Then
  MinValue = Num1
Else
  MinValue = Num2
End If

fn_MinVal = MinValue

Exit_SuperTrap:
   Exit Function

Err_SuperTrap:

   MsgBox Err.Description
   Resume Exit_SuperTrap
End Function


Function fn_MaxVal(ByVal Num1 As Double, ByVal Num2 As Double) As Double
' this VBA function returns maximal value
  Dim MaxValue As Double

On Error GoTo Err_SuperTrap

If (Num1 >= Num2) Then
  MaxValue = Num1
Else
  MaxValue = Num2
End If

fn_MaxVal = MaxValue

Exit_SuperTrap:
   Exit Function

Err_SuperTrap:

   MsgBox Err.Description
   Resume Exit_SuperTrap
End Function

Then you can call this function from SQL statement, for example:
SELECT [ItemCode], [Q1], [Q2], fn_MaxVal([Q1], [Q2]) AS Best_Sales
FROM Sales_2010;

The result will be like this:
ItemCodeQ1Q2Best_Sales
FO63282954239.133258.254239.1
GU523189852.63733257.4733257.4
KH2031474563.911204563.9

UDF functions can greatly enhance your SQL statements. If you are looking for ready VBA function, you can find huge archive of source code in "VBA Developer's Handbook" written by Ken Getz and Mike Gilbert.


sqlexamples.info