SQL advanced -> User defined function in MS-Access SQL query

If you want to use User Defined Function inline function in SQL query - you can do it ! Write your own function in VBA module section of .mdb file. In my sample.mdb I've placed myReverse( ) into mySTRfunctions module. This function recives string and returns reversed one:
Function myReverse(ByVal strWord As String) As String
' this is VBA string reverse function
  Dim strChar, strResult As String
  Dim intPos, intLen As Integer
On Error GoTo Err_SuperTrap
  intPos = 0
  intLen = Len(strWord)
  strResult = ""
If intLen = 0 Then
  myReverse = strWord
  ' if string length = 0, return result now
Else
  For intPos = 0 To intLen - 1
    strChar = Mid(strWord, (intLen - intPos), 1)
    strResult = strResult + strChar
  Next intPos
End If

myReverse = strResult

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 CustomerID, CustomerName, myReverse(CustomerName) AS ReversedName
FROM Customers
WHERE CustomerID < 4

The result will be like this:
CustomerIDCustomerNameReversedName
1Angelina AlbaablA anilegnA
2Jessica SimpsonnospmiS acisseJ
3Barbara SpearssraepS arabraB

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