Approach to Translation
Access surrounds string constants with double quotes ("); SQL Server uses single quotes (').
Change double quotes to single quotes.
Access surrounds date constants with hash marks (#); SQL Server uses single quotes.
Change hash marks to single quotes.
Access lets you enclose identifiers in square brackets (). SQL Server 6.5 doesn't allow square brackets, but SQL Server 7.0 does.
Change square brackets to double quotes. Set the ODBC option to quote identifiers, or set the QUOTED_IDENTIFIER option to ON.
Access lets you use Yes/No columns as Boolean expressions; SQL Server doesn't.
Because the Upsizing Wizard automatically converts Yes/No columns to bits, convert any Access references to Yes/No columns accordingly (e.g., convert x to x <> 0).
Access considers True and False as valid constants; SQL Server doesn't.
Change any expression that compares to True or False to =0 or <>0.
Access allows addition or subtraction of date/time expressions. SQL Server 6.5 doesn't allow arithmetic on date/time data types, but SQL Server 7.0 does.
Change any date/time addition or subtraction occurrences to the equivalent dateadd or datediff functions before conversion.
Access allows Top n. SQL Server 6.5 doesn't support Top n, but SQL Server 7.0 does.
Use set rowcount n instead of Top n, execute the query, and set rowcount 0 to restore normal behavior.
Access allows DISTINCTROW; SQL Server doesn't.
Many users insert the DISTINCTROW predicate into queries, even though it seldom has any effect. If you're certain that the DISTINCTROW is important to the query, recode it with DISTINCT; otherwise, remove it. You might have to include additional columns in the result set to make DISTINCT work.
Access uses column headers in expressions to form other columns; SQL Server doesn't.
Substitute the original expression for the column name. For example, select royalty as r, r*0.05 as tax from dbo_roysched becomes select royalty as r, royalty*0.05 as tax from dbo_roysched.
Access uses the mod operator; SQL server uses the % operator.
Change mod to %.
Access has the Integer division (\) arithmetic operator; SQL Server doesn't.
Change \ to floor (/) operator. For example, x\y becomes floor(x/y).
Access has the XOR, EQV, and IMP logical operators; SQL Server doesn't.
Recode those Access instructions with other SQL Server logical operators.