Mail us
Home » Database Theory » SQL Commands: MS Access SQL Versus SQL Server – The Differences

SQL Commands: MS Access SQL Versus SQL Server – The Differences

SQL Commands: MS Access SQL Versus SQL Server – The Differences

It’s a common mistake when MS Access developers assume their know SQL commands being common to all platforms. Yes, there is a common standard syntax but at the same time, there are variances between software systems.

It’s like choosing a flavour of ice cream; different tastes but it’s still ice cream and if you mix them, again different tastes but may not always be compatible to the palette!

I’ve seem many questions come up on forums about the differences and conversions between MS Access SQL and T-SQL (SQL Sever) which there are of course some differences between the two language syntax’s.

sql commands ms access sql server

So, here’s a few example SQL commands to help you out with or when needing to convert and scale up your Microsoft Access databases.

SQL Commands: MS Access SQL Versus SQL Server – The Differences

String (Literals) are simple to note:
Access:   SELECT This is some text
SQL:        SELECT This is some text
Do note that Access can also use single apostrophe quotes and that SQL should be able to convert literals easily.

Using the LIKE operator to match multiple characters:
Access:   WHERE Column LIKE “*text*
SQL:        WHERE Column LIKE ‘%text%
The key operator character is crucial here and the asterisk will not be compatible.

To match a single character:
Access:   WHERE Column LIKE “?text?
SQL:        WHERE Column LIKE ‘_text_

How about not matching a character or range or characters?
Access:   WHERE Column LIKE “[!a-z]”
SQL:        WHERE Column LIKE ‘[^a-z]’
Replace the exclamation with exponent character.

Working with Date Literals:
Access:    WHERE A_Date = #1/1/2005#
SQL:         WHERE A_Date = 1/1/2005    (this is an implicit conversion from a string to a date)

What about logical values (boolean)?
Access:    WHERE Flag = True  or  False  (Flag is a Yes/No data type)
SQL:         WHERE Flag =1  or    (Flag is a Bit data type)
(Microsoft Access can also use the values 1 and 0 or even Yes and No).

The main areas which will vary will be with the list of built-in functions. Here are some examples:

Working with NULL values:
Access:    NZ(Value, Value_If_Null)
SQL:         COALESCE(Value, Value_If_Null) or ISNULL(Value, Value_If_Null)

Checking for NULL Values:

Access:    WHERE Value IS NULL  or WHERE ISNULL(Value)
(which is difference from SQL’s ISNULL)
SQL:         WHERE Value IS NULL

Parts of text strings:
Access:    MID(StringVal, StartPos, [length] )   (square brackets denotes as optional)
SQL:         SUBSTRING(StringVal, StartPos, length )

Finding a string of text within a string of characters:
Access:    SELECT INSTR(Start, StringToSearch, StringToFind)
SQL:         SELECT CHARINDEX(Start, StringToSearch, StringToFind)

How about a reverse a string of characters?
Access:    SELECT STRREVERSE(StringVal)
SQL:         SELECT REVERSE(StringVal)

When converting a string of characters to either uppercase or lowercase:
Access:    SELECT UCASE(text)LCASE(text)
SQL:         SELECT UPPER(text), LOWER(text)

Some formatting for dates, booleans, numerics as text strings:
Access:    SELECT Format(Value, Format_Type)  (always returns a string value)
SQL:        Don’t do this in SQL; format data at the front-end application or report

Trimming some white unwanted space:
Access:    TRIM(value)
SQL:         RTRIM(LTRIM(value)) (two functions nested together)

Converting data types:
Access:    CINT(value)CDBL(value), CSTR(value), CDATE(value), CBOOL(value)
SQL:         CONVERT(DataType, value) or CAST(value as DataType)

Getting Today’s date and time:
Access:    SELECT now()
SQL:         SELECT getdate()

Creating new dates:
Access:    DATESERIAL(year, month, day)
SQL:         There is no easy way to do this other than to create a function procedure

Date Part Indicators (DateAdd, DateDiff, DatePart):
Microsoft Access and SQL Server both use the same basic date functions (DateAdd, DateDiff, DatePart) but the way you indicate which “date part” you are after differs between the two.

MS Access uses a string expression to indicate the “dart part” in DateAdd, DatePart and DateDiff expressions; SQL Server uses symbols.  Therefore, you need to put quotes around the part name in Access since it is just a string expression, but you should not use any quotes for SQL Server (just enter the value directly).

The Date Part indicators are listed as follows:

Date Part SQL Server MS Access
Year  year, yy, yyyy “yyyy”
Quarter  quarter, qq, q “q”
Month  month, mm, m “m”
Day of Year dayofyear, dy, y “y”
Day  day, dd, d “d”
Week  week, wk, ww “ww”
Day of Week weekday, dw “w”
Hour  hour, hh “h”
Minute  minute, mi, n “n”
Second  second, ss, s “s”
Millisecond  millisecond, ms

Both Access and SQL support the Year(), Month() and Day() functions.

I haven’t even mentioned about joins or parameters and of course, far from covered all the functions. Hopefully this will give some idea of the key differences to SQL commands which of course will vary again should you use a different platform altogether!

You will need to use the SQL reference guides either online or within the host’s application for full more details.

Happy New Year to all!

Tags: , , , , ,

One Response so far.

  1. JC says:

    I wonder if Microsoft or anyone else for that matter has created an automated way to convert Access SQL syntax to SQL Server syntax?
    Similar to those many tools that convert C# to VB and vise versa.

Leave a Reply

Your email address will not be published. Required fields are marked *