|
|
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:
| ItemCode | Q1 | Q2 | Best_Sales |
| FO632829 | 54239.1 | 33258.2 | 54239.1 |
| GU52318 | 9852.63 | 733257.4 | 733257.4 |
| KH203147 | 4563.9 | 1120 | 4563.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.
|