String functionsThe 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) |