Comparison of functions between VBA and T-SQL; how are SQL statements of SQL server used in access? (From http://access911.net)

Comparison of functions between Visual Basic for Applications and SQL Server Transact-SQL (ADP)
Note that the information in this topic applies only to the Microsoft Access Project (. ADP ).

The following table compares common functions in Microsoft Visual Basic for Applications (VBA) and Microsoft SQL Server Transact-SQL. For more information about Transact-SQL scalar functions, see the SQL server documentation.

Note that the "Promotion wizard" converts the following VBA functions (used for table validity rules, field verification, and default) to equivalent Transact-SQL functions.

 

String Functions

VBA functions: Transact-SQL
Scalar functions
------------------------------------------------
ASC (x) ASCII (X)
CHR $ (x) Char (X)
Lcase $ (x) lcase (X)
Lower (X)
Len (x) datalength (X)
Ltrim $ (x) ltrim (X)
Mid $ (x, y, z) substring (x, y, z)
Right $ (x, y) Right (x, y)
Rtrim $ (x) rtrim (X)
Space $ (x) Space (X)
STR $ (x) STR (X)
Ucase $ (x) ucase (X)
Upper (X)

 

Conversion functions

VBA functions: Transact-SQL
Scalar functions
------------------------------------------------
Ccur (x) convert (money, X)
Cdbl (x) convert (float, X)
CINT (x) convert (smallint, X)
Clng (x) convert (INT, X)
Csng (x) convert (Real, X)
CSTR (x) convert (varchar, X)
Cvdate (x) convert (datetime, X)

 

 

Date Functions

VBA functions: Transact-SQL
Scalar functions
------------------------------------------------
Date (x) convert (datetime, convert (varchar, getdate (X )))
Dateadd ("<access datepart>", x, y) dateadd (<SQL Server datepart>, x, y)
Datediff ("<access datepart>", x, y) datediff (<SQL Server datepart>, x, y)
Datepart ("<access datepart>", x) datepart (<SQL Server datepart>, X)
Day (x) datepart (DD, X)
Hour (x) datepart (HH, X)
Minute (x) datepart (MI, X)
Month (x) datepart (mm, X)
Now (x) getdate (X)
Second (x) datepart (SS, X)
Weekday (x) datepart (DW, X)
Year (x) datepart (YY, X)

 

 

mathematical functions
VBA functions: Transact-SQL
scalar functions
--------------------------------------------
int (x) floor (x)
SGN (X) sign (x)
RND (x) round (x)

other functions sorted by access911
VBA function: Transact-SQL
scalar function
--------------------------------------------
replace Replace
mid substring
NZ isnull