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 ...

Full article...