|
|
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:
| CustomerID | CustomerName | ReversedName |
| 1 | Angelina Alba | ablA anilegnA |
| 2 | Jessica Simpson | nospmiS acisseJ |
| 3 | Barbara Spears | sraepS 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.
|