T-SQL Equivalents for Microsoft Access VBA Functions

March 1, 2010

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers