Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rating: Thread Rating: 13 votes, 5.00 average. Display Modes
Old 08-22-2013, 02:02 PM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Concatenate Access SQL to TSQL (SQL Server)

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.

Code:
'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/featu...-Functions.htm

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Reply

Tags
"&" , access sql , sql server , tsql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Debugging TSQL ions SQL Server 3 10-05-2011 05:39 AM
Access to SQL Server Migration Assistant - SQL Server 2000 The Brown Growler SQL Server 5 10-18-2009 04:06 PM
CONCATENATE in access ylivne Queries 4 07-07-2009 05:30 AM
Testing String Length TSQL doco SQL Server 1 03-15-2007 07:12 AM
Sending an SQLServer TSQL command from VBA Waltang Modules & VBA 2 05-22-2003 05:25 AM




All times are GMT -8. The time now is 07:18 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World