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.
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 0 (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:
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”|
|Minute||minute, mi, n||“n”|
|Second||second, ss, s||“s”|
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!