Note The information in this topic applies only to a Microsoft Access project (.adp).
The following tables compare commonly-used functions in Microsoft Visual Basic for Applications (VBA) and Microsoft SQL Server Transact-SQL. For more information on Transact-SQL scalar functions, see the SQL Server documentation.
Note The Upsizing Wizard converts the following VBA functions (which can occur in table validation rules, field validation, and defaults) to their 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) |
Math functions
| VBA functions |
Transact-SQL
scalar functions |
| Int(x) |
FLOOR(x) |
| Sgn(x) |
SIGN(x) |
| Rnd(x) |
ROUND(x) |
United States
Email this link
Printer-Friendly Version
Share
Hide All
