Migrating to ADP

I am in the process of migrating from MDB to ADP and I am having problems
with the query builder and the syntax of the SQL. Somethings I know like "*"
is replaced by "%" and dates are don't use the "#".

Other things I can't figure out. An example is when I use a criteria on a
text field the criter ends up as = N'Mike ALLEN'. What does the N stand for
after the = sign?

I also cannot seem to use the IIF statement in my queries now.

In short can anyone recommend a good book or source to help me through the
transistion?

My backend is an SQL database at DiscountASP.net

"BillS" wrote:
I am in the process of migrating from MDB to ADP and I am having problems
with the query builder and the syntax of the SQL. Somethings I know like
"*"
is replaced by "%" and dates are don't use the "#".

Other things I can't figure out. An example is when I use a criteria on a
text field the criter ends up as = N'Mike ALLEN'. What does the N stand
for
after the = sign?

I also cannot seem to use the IIF statement in my queries now.

In short can anyone recommend a good book or source to help me through the
transistion?

Hi Bill,

Here are some quick notes I saved for
myself when first switching to SQL Server
(plus some got added over time), i.e.,
my "cheatsheet":

1) No TRIM
LTRIM(RTRIM([field])
2) No MID
SUBSTRING([field], start, length)

CAST(REPLACE(dbo.VW_Textbooks.tISBN, '-', '') AS char(10)) AS
ISBNNoHyph,
CAST(SUBSTRING(REPLACE(dbo.VW_Textbooks.tISBN, '-', ''), 1, 9) AS int)
AS ISBN9,
CAST(SUBSTRING(dbo.VW_Textbooks.tISBN, 13, 1) AS char(1)) AS ISBNC

3) No Nz nor IIF(IsNull(a),b)

ISNULL(a,0.0) //if a is null, returns 0.0

COALESCE(a,b)
// returns first nonNULL expression among its arguments;
// any number of arguments;
// if all NULL, returns NULL

CASE equivalent of COALESCE:

CASE
WHEN (arg1 IS NOT NULL)
THEN arg1
........
WHEN (argn IS NOT NULL)
THEN argn
ELSE NULL
END

so NZ([f1],0) -- COALESCE([f1],0) // if [f1] is null, returns 0

NULLIF(arg1, arg2)
// returns null (type of first argument) if its 2 arguments are
equal
// returns first argument if they are not equal


COALESCE(NULLIF(val2000,0), NULLIF(val2001,0), NULLIF(val2003,0))
// if val2000 = 0, coalesce moves on to next expression

4) do not concatenate with "&"

use "+"

5) LEN() ignores trailing blanks

use DATALENGTH()

6) No CStr

CONVERT(char(12), GETDATE(), 3)
// style 3 = dd/mm/yy
// style 103 = dd/mm/yyyy (British/French)
// style 102 = yyyy.mm.dd (ANSI)
// style 1 = mm/dd/yy (USA)
// style 101 = mm/dd/yyyy (USA)
// style 110 = mm-dd-yyyy (USA)
// style 8 = hh:mm:ss
// style 100 = mon dd yyyy hh:mi AM or PM (default)
// style 109 = mon dd yyyy hh:mi:ss:mmm AM or PM (default +
miilisecs)
// style 107 = Mon dd, yyyy
// style 120 = yyyy-mm-dd hh:mi:ss (24hr) (ODBC canonical)

CAST([field] AS Char(50))

7) DATES
No Date(), Now()
use GETDATE()

CONVERT(datetime, GETDATE(), 101)

delimiter = single quote (not "#")
// WHERE OrderDate = 'Sep 13 1996'
// WHERE Dte = '20021221'
// WHERE PostDate = CONVERT(DATETIME, '2003-08-18 00:00:00',
102)

8) LIKE '%abc%' must use single quotes

9) No FORMAT

CONVERT(datatype, [length], expression, [style])

STR(float_expression, [length, decimal]])
//when "length" of exp length, returns **
//length (default=10) must be = # of digits of expr before
decimal pt
//STR(123.45,6,1) = space123.5
//STR(12,4) = spacespace12

REPLACE(STR(@Num, 11, 2), SPACE(1), '0')
// pad float with zeroes on left

10) "n" = Unicode (PITA)
change all nxxx fields to non-Unicode fields
nchar ----- char
nvarchar ------ varchar
ntext --------- text

N'foo' N prefix converts string to Unicode
(i.e., means "here comes a Unicode string constant.")
Some stored procs require a Unicode string.

INF: Unicode String Constants in SQL Server Require N Prefix
http://support.microsoft.com/?*id=239530
"The "N" prefix stands for National Language in the SQL-92
standard,
and must be uppercase. If you do not prefix a Unicode string
constant with N,
SQL Server will convert it to the non-Unicode code page of the
current database
before it uses the string."

11) CASE WHEN
//when designing a View, insert CASE WHEN's last
// because designer cannot graphically represent them
//so from then on you must work only with SQL

12) to return View with ORDER BY,
you must use

SELECT TOP 100 PERCENT

(not recommended, may end up slowing server down
as data grows larger.from Access, use query on View
and set order by in query)

13) multiple Criteria in View Designer

to get
WHERE (dbo.t.f1=1 OR dbo.t.f1=4)
AND (dbo.t.f2=1)

Column Criteria
f1 =1 OR =4
f2 =1

14) SQL to remove data from table
"DELETE FROM table" --- NO "*" in SQL!!!!!
(also, "FROM" keyword is optional."DELETE table")

-- in a stored proc to delete records from table in ADP,
all records may not be deleted due to ADP record limit
setting unless you set rowcount to 0

CREATE PROC myProc
AS
SET NOCOUNT ON
-- Delete really all records you wish
SET ROWCOUNT 0
-- Now the delete-statement
DELETE myTable WHERE Condition
GO



15) ComboBox "GoTo" subroutines may no longer work

===========
mdb example:
===========
-------------------------------------------------
Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs as Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
-------------------------------------------------

===========
adp example:
===========
-------------------------------------------------
Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs as ADODB.Recordset

Set rs = Me.Recordset.Clone
rs.Find "[ID] = " & Me![Combo1], , , 1
Me.Bookmark = rs.Bookmark

End Sub
-------------------------------------------------

16) Start *ALL* Stored Procedures with

SET NOCOUNT ON

This will prevent "(n) row(s) affected" messages
being sent back and being misinterpreted as a
returned recordset.

///////////////////////////////////

As far as good books, I got a lot from Mary Chipman's
"Microsoft Access Developer's Guide to SQL Server"

good luck,

gary

SQL Server uses a SQL dialect known as Transact-SQL or T-SQL that has many
differences from Jet SQL. You can find complete documentation on T-SQL at
....

http://msdn.microsoft.com/library/de...qlcon_6lyk.asp

.... or you can download SQL Server Books Online at ...

http://www.microsoft.com/sql/techinf...2000/books.asp

--
Brendan Reynolds (MVP)

"BillS" wrote in message
...
I am in the process of migrating from MDB to ADP and I am having problems
with the query builder and the syntax of the SQL. Somethings I know like
"*"
is replaced by "%" and dates are don't use the "#".

Other things I can't figure out. An example is when I use a criteria on a
text field the criter ends up as = N'Mike ALLEN'. What does the N stand
for
after the = sign?

I also cannot seem to use the IIF statement in my queries now.

In short can anyone recommend a good book or source to help me through the
transistion?