BillS said:
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) = <space>123.5
//STR(12,4) = <space><space>12
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
