Concatenate Access SQL to TSQL (SQL Server)

[Rx_]

For those who Link Tables to SQL Server as the database Back End (BE), a SQL View is basically a SQL Query with additional option to add security. A View can also become a Linked Table.

The advantage of using a View as a linked table? It is very fast. Then running a query on the Access side against the SQL Server View - the ODBC is very efficient with the SQL conversion language. Huge speed gain.

T-SQL is much less forgiving than Access SQL. T-SQL 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 literal string "the order number is " with the integer value 90210, the result is an error. Numbers, currency and date values must be converted to string values to be concatenated to other strings, like this ...
The error in TSQL is: Conversion failed when converting the nvarchar value 'the order number is ' to data type int.

MS Access SQL
Select "the order number is " & tblOrder.OrderID
SQL Server TSQL
SELECT 'the order number is ' + CONVERT(VARCHAR(8), tblOrder.OrderID)

Notice the single quote ( ' ) vs the double quote ( " ) and the ( & ) vs ( + )

Using Access specific functions (e.g IIF) has horrible results with ODBC Linked table queries. Straight SQL against a SQL Linked Table can be very near running the query all in TSQL.


---------- Additional Conversions for Access SQL to SQL Server TSQL -----
For complex queries used often, turn them into SQL Views and take the time to make the conversion. Here is a list of 42.74% of what an Access user needs to know to make the conversion.

'Date()
Debug.Print "Today's date is " & Date
Debug.Print "The time now is " & Now()
' SELECT GETDATE() AS TheResult
' 2013-22-08 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 (8/22/2013) would be 8 + 22 + 2013 = 2043
' 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

Shameless Borrowed from a 2010 post at:
http://www.databasejournal.com/feat...alents-for-Microsoft-Access-VBA-Functions.htm