T-SQL Equivalents for Microsoft Access VBA Functions

If you need to migrate your Access application to SQL
Server, don’t count on The SQL Server Upsize Wizard in Microsoft Access to automatically
convert your VBA functions. If you want to push the complex query processing
done by your Access queries to the back end, you’ll have to rewrite them in
T-SQL.

I was introduced to SQL Server version 7.0 a decade ago when I was asked to convert all of the queries in a shrink-wrapped, production ready
Access application that managed room reservations. I had never written a line
of T-SQL, never built a stored procedure or even a view for that matter but I
wasn’t worried. I mean, how difficult can it be? It’s all SQL language, isn’t
it?

Actually no, it isn’t. Microsoft Access allows users to
embed VBA functions in the SQL query language. From things as simple as Date()
to things more complicated like the IIf() function. Access also allows you to
leverage your own VBA functions right from the query, like custom functions to
perform calculations or validate phone and email strings.

Doesn’t the SQL Server Upsize Wizard in Microsoft Access
automatically convert these queries? No, sadly, it doesn’t even try. If you
want to push the complex query processing done by your Access queries to the
back end, you’ll have to rewrite them in T-SQL. If that’s a task you have in
front of you, then the cheat-sheet below is really, really going to help.

High Level Overview

Without beating the details to death, here is a quick
reference guide to the T-SQL commands that map to the Access functions we are
most familiar with. If unsure what the options are for the T-SQL functions,
simply type the function keyword and press F1 to bring up Books Online help.

Category

Access
fn

T-SQL
fn

Date

Date()

GETDATE()

Date

DateAdd()

DATEADD

Date

DateDiff()

DATEDIFF

Date

Day()

DAY

Date

Month()

MONTH

Date

Year()

YEAR

Date

IsDate()

ISDATE

Numeric

Abs()

ABS

Numeric

IsNumeric()

ISNUMERIC

Numeric

Round()

ROUND

String

Asc()

ASCII

String

Chr()

CHAR

String

InStr()

CHARINDEX

String

InStr()

PATINDEX

String

LCase()

LOWER

String

Left()

LEFT

String

Len()

LEN

String

Mid()

SUBSTRING

String

Nz()

ISNULL

String

Replace()

REPLACE

String

Right()

RIGHT

String

Space()

SPACE

String

String()

REPLICATE

String

Trim()

LTRIM

String

Trim()

RTRIM

String

Ucase()

UPPER

Bonus

Iif()

CASE

Watch Out for These Gotcha’s

There are a couple of tricks to watch out for. First, the
function signatures sometimes match the VBA ones exactly, but don’t expect that
to be the case every time. If you don’t get the results you expect, review the
help file again to make sure you’re passing arguments in the correct order.

Second, T-SQL is much less forgiving than Access. In T-SQL
one uses the plus operator (+) to do both concatenation and addition. Numbers
are automatically added together. Text strings are automatically concatenated.
But if you try to "plus" the string "the order number is "
with the integer value 2345345, you’ll get an error. Numbers, currency and
date values must be converted to string values to be concatenated to other
strings, like this …

   
SELECT 'the order number is ' + CONVERT(VARCHAR(8), tblOrder.OrderID)

Lastly, managing dates in T-SQL is going to seem like a
carnival ride in Hell after having worked with dates in Access and VBA. Sure,
there are some SQL developers out there with thousands of lines of library code
who will try to convince you that anything can be done easily, but that’s only
because they’ve already paid the price. You haven’t, so it’s going to hurt. Never
fear, though, because we have the almighty Google. Need a script to find the
last day of a month? Google it. Gotta count "work days"? Google
it. Whatever result you end up with will most likely be clumsy, but it’s
usually not difficult to find something that does actually work.

The Devil is in the Details

What follows below is the contents of a module I constructed
in Microsoft Access where I built, ran and tested each of the functions listed
above. Beneath each Debug.Print call is an example using the T-SQL equivalent
of the Access function being executed.

If you’re ambitious, copy and paste the following into an
empty function in an Access module and give it a run. If you’re really, really
ambitious, paste it into an empty query in SQL Server Management Studio and try
running all the SELECT statements. You’ll see that they do indeed produce the
same result as their Access counterparts.


‘Date()
Debug.Print “Today’s date is ” & Date
Debug.Print “The time now is ” & Now()
‘ SELECT GETDATE() AS TheResult
‘ 2010-02-09 13:33:33.163

Debug.Print “Is the text ‘Feb 11 2024’ a date? ” & IsDate(“Feb 11 2024”)
Debug.Print “Is the text ‘987.123’ a date? ” & IsDate(“987.123”)
‘ SELECT ISDATE(‘Feb 11 2024’), ISDATE(‘987.123’)

‘DateAdd()
Debug.Print “Now plus one day is ” & DateAdd(“d”, 1, Now())
‘ SELECT DATEADD(DAY, 1, GETDATE()) AS TheResult ‘ 2010-02-09 13:33:33.163

‘DateDiff()
Debug.Print “Minutes diff is ” & DateDiff(“n”, #2/8/2010 1:33:00 PM#, Now())
‘ SELECT DATEDIFF(N, ‘2010-02-08 13:33:33’, GETDATE())

‘Day(), Month() & Year()
Debug.Print “D-” & Day(Date) & ” M-” & Month(Date) & ” Y-” & Year(Date)
‘ SELECT DAY(GETDATE()), MONTH(GETDATE()), YEAR(GETDATE())

‘ /////////////////////////// T-SQL WARNING ///////////////////////////
‘ Be careful about concatonation in SQL. The plus operator (+) is used
‘ in place of the ampersand (&) but performs addition on numbers. So
‘ this next SQL will evalulate to the sum of today’s date parts, which
‘ at the time of this writing (2/8/2010) would be 2 + 8 + 2010 = 2020
‘ SELECT DAY(GETDATE()) + MONTH(GETDATE()) + YEAR(GETDATE())
‘ /////////////////////////// T-SQL WARNING ///////////////////////////

‘Abs()
Debug.Print “The absolute value of -123 is ” & Abs(-123)
‘ SELECT ABS(-123)

‘IsNumeric()
Debug.Print “Is the text ‘Monday’ a number? ” & IsNumeric(“Monday”)
Debug.Print “Is the text ‘987.123’ a number? ” & IsNumeric(“987.123”)
‘ SELECT ISNUMERIC(‘Monday’), ISNUMERIC(‘987.123’)

‘Round()
Debug.Print “One can round 345.3456 to ” & Round(345.3456, 2)
‘ SELECT ROUND(345.3456,2)

‘Asc(), Chr()
Debug.Print “The letter ‘G’ is ASC ” & Asc(“G”)
Debug.Print “ASCII 71 has a Char value of ‘” & Chr(71) & “‘”
‘ SELECT ASCII(‘G’) AS TheNumber, CHAR(ASCII(‘G’)) AS TheString
‘ TheNumber = 71 and TheString = ‘G’

‘InStr()
Debug.Print “Where does ‘pqr’ start in ‘abcpqrxyz’? ” & InStr(1, “abcpqrxyz”, “pqr”)
‘ SELECT CHARINDEX(‘pqr’, ‘abcpqrxyz’, 0)
‘ SELECT PATINDEX(‘%pqr%’, ‘abcpqrxyz’)

‘Mid()
Debug.Print “The middle 3 chars of ‘abcpqrxyz’ are: ” & Mid(“abcpqrxyz”, 4, 3)
‘ SELECT SUBSTRING(‘abcpqrxyz’, 4, 3)

‘LCase(), Ucase()
Debug.Print LCase(“Lamont Cranston”) & ” – or – ” & UCase(“Lamont Cranston”)
‘ SELECT LOWER(‘Lamont Cranston’), UPPER(‘Lamont Cranston’)

‘Left(), Right()
Debug.Print Left(“abcpqrxyz”, 4) & ” – or – “; Right(“abcpqrxyz”, 4)
‘ SELECT LEFT(‘abcpqrxyz’, 4), RIGHT(‘abcpqrxyz’, 4)

‘Trim()
Debug.Print “Better than T-SQL … ” & Trim(” both trim “)
‘ SELECT LTRIM(RTRIM(‘ both trim ‘)) AS TheString

‘Space(), String()
Debug.Print String(5, “W”) & Space(3) & String(5, “O”) & Space(3) & String(5, “W”)
‘ SELECT REPLICATE(‘W’,5) + SPACE(3) + REPLICATE(‘O’,5) + SPACE(3) +REPLICATE(‘W’,5)

‘Len()
Debug.Print “How big is ‘supercalifragalistic’? ” & Len(“supercalifragalistic”)
‘ SELECT LEN(‘supercalifragalistic’) AS TheValue

‘Nz()
Debug.Print “Replace Null with something: ” & Nz(Null, ” … that is not null”)
‘ SELECT ISNULL(NULL, ‘this is something’ AS NullResult

‘Replace()
Debug.Print “Replace something with something: ” & Replace(“abcpqrxyz”, “pqr”, “999”)
‘ SELECT REPLACE(‘abcpqrxyz’,’pqr’,’999′) AS ReplaceResult

‘IIf()
Debug.Print “The IIf() function is harder: ” & IIf(1 = 0, “Got Math?”, “Correct”)
‘ SELECT CASE WHEN 1=0 THEN ‘Got Math?’ ELSE ‘Correct’ END AS IIFResult

Worth the Effort

The chart above, with the T-SQL versions of your favorite
VBA functions, will come in handy if you have to migrate your Access
application to SQL Server, but some may wonder if it’s worth the effort. The
answer is an unqualified yes. If your data is in SQL Server then pushing the
processing to the server will always yield a performance gain.

Perhaps that last bit of advice is a little unsolicited.
This article didn’t bother to ask “why” you want to convert your VBA functions
to T-SQL, but it’s a good question and probably worthy of its own article,
albeit a short one. The first Access-to-SQL conversion I did over a decade ago
revealed to me that there are times you simply can’t use a stored proc as a recordsource
and times where it was simply easier to use the table instead of a view. So
the point is moot where T-SQL features simply can’t be employed, but everywhere
else it’s going to pay off to learn to write your code in the native language
of the data, not the data consumer.

»


See All Articles by Columnist
Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles