Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects.  Here's a few handy things to help you out with converting your projects.  Check in now and then as this short list will eventually grow as more things come up.

Converting NULL values

Access:        NZ(Value, ValueToReturnIfNull)
T-SQL:         COALESCE(Value, ValueToReturnIfNull) – or –  ISNULL(Value, ValueToReturnIfNull)

Checking for NULLs

Access:      WHERE Value IS NULL   – or –  WHERE ISNULL(Value)   (note the difference from T-SQL's ISNULL)
T-SQL:       WHERE Value IS NULL
       
String Segments

Access:       MID(StringVal, StartPos, [length] )   (length is optional)
T-SQL:        SUBSTRING(StringVal, StartPos, length )    (length is required!)

Finding a String within a String

Access:     SELECT INSTR(start, StringToSearch, StringToFind)
T-SQL:       SELECT CHARINDEX(start, StringToSearch, StringToFind)

Reverse a String

Access:     SELECT STRREVERSE(StringVal)
T-SQL:       SELECT REVERSE(StringVal)

Convert a String to Uppercase or Lowercase

Access:      SELECT UCASE(StringVal),  LCASE(StringVal)
T-SQL:       SELECT UPPER(StringVal), LOWER(StringVal)

Formatting Dates, Booleans, Numerics as Strings

Access:     SELECT Format(Value, FormatSpecification)  (note: this always returns a string value)
T-SQL:      Do not do this in T-SQL; format data at your front-end application or report

String Literals

Access:      SELECT "This is a string"
T-SQL:       SELECT 'This is a string'

LIKE pattern matching

matching multiple characters:

Access:      WHERE Column LIKE "string" 
T-SQL:       WHERE Column LIKE '%string%'

matching a single character:

Access:     WHERE Column LIKE "?string?"
T-SQL:       WHERE Column LIKE 'string'

not matching a character or range:

Access:   WHERE Column LIKE "[!a-z]"
T-SQL:     WHERE Column LIKE '[^a-z]'

Triming White Space

Access:       TRIM(val)
T-SQL:        RTRIM(LTRIM(val))

Converting DataTypes

Access:       CINT(value),  CDBL(value), CDEC(value),  CSTR(value), CDATE(value), CBOOL(value)
T-SQL:        CONVERT(DATATYPE, value) – or – CAST(value AS datatype)

Conditional Expressions

Access:       IIF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL:        CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END

Working with Date Literals

Access:      WHERE SomeDate = #1/1/2005#
T-SQL:       WHERE SomeDate = '1/1/2005'    (this is an implicit conversion from a string to a date)

Creating new Dates

Access:     DATESERIAL(year,month,date)
T-SQL:       Use the Date() function  here  – there is no quick easy way to do this in T-SQL

Creating new Times

Access:     TIMESERIAL(Hour, minute, second)
T-SQL:       Use the Time() function here  – there is no quick easy way to do this in T-SQL

Getting Today's Date and Time

Access:     SELECT now()
T-SQL:      SELECT getdate()

Getting Today's Date only (i.e., at midnight)

Access:     SELECT date()
T-SQL:      Use the DateOnly() function here  :  SELECT dbo.DateOnly(getdate())

Getting Today's Time Only (at the "base" date, or date with a numeric value of 0)

Access:    SELECT Time()    (this returns the time at 12/30/1899)
T-SQL:      Use the TimeOnly() function here  :  SELECT dbo.TimeOnly(getdate())  (returns the time at 1/1/1900)

Boolean (True/False) Values

Access:      WHERE Active = True  – and –  WHERE Active = False
                    (Active is a Boolean datatype)

T-SQL:       WHERE Active=1   – and –   WHERE Active=0
                    (Active is a Bit datatype)

Returning or Setting Boolean Values

Access:    SELECT BooleanExpression
T-SQL:      CAST(CASE WHEN BooleanExpression THEN 1 ELSE 0 END) AS BIT

FULL OUTER JOINS

(Note: try to avoid these as a general practice)

Access:      SELECT … FROM tableA LEFT OUTER JOIN tableB ON …
                     UNION ALL
                    SELECT … FROM tableB LEFT OUTER JOIN tableA ON … WHERE tableA .PK IS NULL

T-SQL:       SELECT … FROM tableA FULL OUTER JOIN tableB ON ….

RIGHT OUTER JOINS

Because we all know that using the query designer in Access sometimes results in these, but we should never use them in manually written and maintained SQL:

Access:      SELECT … FROM tableA RIGHT OUTER JOIN tableB ON ….
T-SQL:        SELECT … FROM tableB LEFT OUTER JOIN tableA ON ….

Parameters

Access:      SELECT [Any column name not defined] 
T-SQL:       SELECT @ParamName

Modulo Operator

Access:     SELECT value1 MOD value2
T-SQL:      SELECT value1 % value2

Dividing Integers to calculate a Percentage or other result with decimal places

Access:    SELECT Int1 / Int2    (this returns a Double value implicitly)
T-SQL:      SELECT Int1 * 1.0 / Int2   (the multiplication by 1.0 results in a numeric(8,6) being returned)

String Concatenation Operator

Access:      Val1 & Val2  (both will be implicitly converted to strings if they are not already)
T-SQL:       Val1 + Val2 ( note that explicit conversion to a "string" datatypes is necessary in T-SQL)

Referencing an Expression in a SELECT

Here, we define A+B as a new column X, and we want to reference X in the SELECT:

Access:      SELECT A+B as X, X+C as D FROM …
T-SQL:        SELECT X, X+C as D FROM (SELECT A+B as X, C FROM … ) tmp

Getting a Character from an ASCII code

Access:      SELECT CHR(AsciiCode)
T-SQL:        SELECT CHAR(AsciiCode)

Getting an ASCII code from a Character

Access:      SELECT ASC(Character)
T-SQL:        SELECT ASCII(Character)

Date Part Indicators (DateAdd, DateDiff, DatePart)

MS Access and SQL Server both use the same basic date functions (DateAdd, DateDiff, DatePart) but the way you indicate which "date part" you are after differs between the two.

MS Access uses a string expression to indicate the "dart part" in DateAdd, DatePart and DateDiff expressions; SQL Server uses symbols.  Thus, you need to put quotes around the part name in MS Access since it is just a string expression, but you should NOT use quotes in SQL Server – just enter the value directly.

The Date Part indicators are listed below: