Core SQL Dialect Differences
A selection of fundamental differences of SQL Server's implementation of Structured Query Language from Jet:
T-SQL string delimiter uses only single quote.
Jet: SELECT * FROM Orders WHERE ShipCountry = "France"
SS : SELECT * FROM Orders WHERE ShipCountry = 'France'
Standard Wildcard Characters are % and _ instead of * and ?
Jet: … WHERE ShipCountry Like "F*"
SS : … WHERE ShipCountry Like 'F%'
Dates are also delimited through single quote ' and not octothorpe #. US format remains the default.
Jet: … WHERE OrderDate Between #12/01/2007# AND #12/31/2007#
SS : … WHERE OrderDate Between '12/01/2007' AND '12/31/2007'
String concatenation uses + symbol and not & (as can be used in Jet) - Nulls therefore do propagate.
Jet: SELECT FirstName & " " & LastName As FullName FROM Employees
SS : SELECT FirstName + ' ' + LastName As FullName FROM Employees
For such circumstances the Access Nz function maps to T-SQL IsNull (or the more powerful Coalesce)
Jet: SELECT Nz(Region, 'N/A') FROM Employees
SS : SELECT IsNull(Region, 'N/A') FROM Employees
VBA's IsNull is not exactly represented (however in either SQL dialect "Is Null" is a better choice)
Jet: SELECT * FROM Employees WHERE Region Is Null
SS : SELECT * FROM Employees WHERE Region Is Null
VBA's Immediate If function is implemented with ANSI SQL Case statements
Jet: SELECT IIF(Region Is Null, 'N/A', Region) FROM Employees
SS : SELECT CASE WHEN Region Is Null THEN ' N/A' ELSE Region END FROM Employees
VBA Type conversion functions can be handled with CAST or CONVERT calls
Jet: SELECT CStr([Birth Date]) FROM Employees
SS : SELECT CONVERT(varChar, Region) FROM Employees
Data Types in T-SQL are *not* implicitly converted for you!
e.g., Jet will silently convert the numeric value to concatenate Text and Integer fields in a SQL statement
Jet: SELECT "Order Dated - " & [Order Date] As FullNameRef FROM Orders
SS : SELECT 'Order Dated - ' + CONVERT(varChar, [Order Date]) As FullNameRef
Boolean (Yes/No) Data Types in Jet (stored as 0 or -1) are represented by Bit fields in SQL Server (stored as 0 or 1). An ODBC linked table will perform the conversion for you – but if you're writing the SQL yourself you need to know. T-SQL also has no "True" and "False" literal constants.
Jet: SELECT * FROM Products WHERE Discontinued = True
SS : SELECT * FROM Products WHERE Discontinued = 1
The aliasing keyword "AS" is optional in T-SQL for both tables and columns – in Jet tables only.
Jet: SELECT [Unit Price] * Quantity As TotalAmt FROM [Order Details] tblDetails
SS : SELECT [Unit Price] * Quantity TotalAmt FROM [Order Details] tblDetails
You cannot reference aliased columns in T-SQL
Jet: SELECT [Unit Price] * Quantity As TotalAmt, TotalAmt/10 As Installment FROM [Order Details]
SS : SELECT [Unit Price] * Quantity As TotalAmt, ([Unit Price] * Quantity)/10 As Installment FROM [Order Details]
