Visual Basic Access (VBA) built-in functions include some ANSI standard functions that have similar SQL Server equivalents, like Asc and Atn, and some non-standard functions like Format and IIf that have no direct SQL Server equivalents, and some platform dependent functions, like Environ and CodeDB that are not applicable to SQL Server.
Standard built-in functions are implemented by substituting the SQL Server verb for the Access one, ex. Asc is replaced by ASCII Many non-standard Access functions, like IIf and CInt are implemented by Scriptoria by replacing the built-in functions with inline SQL Server equivalent constructs.
|
VB Function |
SQL Server Equivalent |
Implementation |
|
& |
+ |
Inline |
|
Asc |
ASCII |
Inline |
|
AscB |
|
not supported |
|
AscW |
|
CLR |
|
Atn |
ATAN |
Inline |
|
Avg |
AVG |
inline |
|
CBool |
|
CLR |
|
CByte |
CAST |
Inline |
|
CCur |
CAST |
Inline |
|
CDate |
CAST |
inline |
|
CDbl |
CAST |
Inline |
|
CDec |
CAST |
Inline |
|
Choose |
|
not supported |
|
Chr, Chr$ |
CAST |
Inline |
|
ChrB, ChrB$ |
|
not supported |
|
ChrW, ChrW$ |
|
CLR |
|
CInt |
CAST |
Inline |
|
CLng |
CAST |
Inline |
|
Cos |
COS |
inline |
|
CSng |
CAST |
Inline |
|
CStr |
STR |
Inline |
|
Current User |
CURRENT_USER |
inline |
|
CVar |
|
not supported |
|
CVDate |
CAST |
Inline |
|
Date, Date$ |
GETDATE() |
Inline |
|
DateAdd |
|
Inline |
|
DateDiff |
|
Inline/CLR see Note-2 |
|
DatePart |
|
Inline/CLR see Note-2 |
|
DateSerial |
|
CLR |
|
DateValue |
|
CLR |
|
Day |
|
inline |
|
DDB |
|
CLR |
|
Environ |
|
not supported |
|
Exp |
EXP |
inline |
|
Fix |
CEILING |
Inline |
|
Format, Format$ |
|
CLR see Note-3 |
|
FV |
|
CLR |
|
Hex, Hex$ |
HEX |
Inline |
|
Hour |
|
inline |
|
IIf |
|
|
|
InStr |
|
CLR |
|
InStrB, InStr$ |
|
not supported |
|
InStrRev |
|
CLR |
|
|
FLOOR |
Inline |
|
IPmt |
|
CLR |
|
IRR |
|
not supported |
|
IsDate |
ISDATE |
not supported |
|
IsNull |
|
Inline |
|
IsNumeric |
ISNUMERIC |
inline |
|
LCase, LCase$ |
LCASE |
Inline |
|
Left, Left$ |
LEFT |
inline |
|
LeftB, LeftB$ |
|
not supported |
|
Len |
LEN |
Inline |
|
LenB |
|
Inline |
|
Log |
LOG |
inline |
|
LTrim, LTrim$ |
LTRIM |
Inline |
|
Mid, Mid$ |
SUBSTR |
Inline |
|
MidB, MidB$ |
|
not supported |
|
Month |
|
inline |
|
MonthName |
|
CLR |
|
Now |
GETDATE() |
Inline |
|
Nz |
|
inline see note-4 |
|
Oct, Oct$ |
|
not supported |
|
Partition |
|
CLR |
|
Pmt |
|
CLR |
|
PPmt |
|
CLR |
|
PV |
|
CLR |
|
Rate |
|
CLR |
|
Replace |
|
not supported |
|
RGB |
|
not supported |
|
Right, Right$ |
RIGHT |
inline |
|
RightB, RightB$ |
|
not supported |
|
Rnd |
RAND |
Inline |
|
RTrim, RTrim$ |
RTRIM |
Inline |
|
Second |
|
inline |
|
Sgn |
SIGN |
Inline |
|
Sin |
SIN |
inline |
|
SLN |
|
CLR |
|
Space, Space$ |
SPACES |
Inline |
|
SPC |
|
not supported |
|
Split |
|
not supported |
|
Sqr |
SQRT |
Inline |
|
Str, Str$ |
STR |
Inline |
|
StrComp |
|
CLR |
|
StrConv |
|
CLR |
|
String, String$ |
REPLICATE |
inline |
|
StrReverse |
|
CLR |
|
Switch |
|
|
|
Tan |
TAN |
inline |
|
Time, Time$ |
GETDATE |
Inline |
|
TimeSerial |
|
CLR |
|
TimeValue |
GETDATE |
CLR |
|
Trim, Trim$ |
RTRIM(LTRIM |
inline |
|
UCase, UCase$ |
UCASE |
Inline |
|
Val |
|
CLR |
|
Weekday |
|
inline / CLR see note-2 |
|
WeekdayName |
|
CLR |
|
Year |
|
inline |
Note-1: CLR (Common Runtime Language) user-defined functions are available when converting to MS-SQL 2005 only. Corresponding MS-SQL scalar functions are only deployed when VBA functions are encountered in Access queries.
Note-2: When converting to MS-SQL 2000, firstdayofweek and/or firstweekofyear parameters are removed and a warning message is issued. Use the MS-SQL SET DATEFIRST to set the @@DATEFIRST value, which is equivalent to firstweekofyear parameter. There is no MS-SQL equivalent for firstweekofyear.
Firstdayofweek and/or firstweekofyear parameters are supported when converting to MS-SQL 2005. The VB.Net DateDiff, DatePart and Weekday functions are called using CLR user-defined functions.
Note-3: Using the MS-Access Format function should be avoided. Formatting numbers, dates and text should normally be performed by the front-end. Use the Format Property on Forms and Reports instead of using the Format function in QueryDefs.
Some uses of the Format function can be reformulated using other supported built-in functions. For example, Format(Date(),"yyyy") is equivalent to DatePart("yyyy", Date()) or Year(Date()).
Note-4: The Nz function is converted using the CASE statement.
VBA "IIf()" or "Switch()" functions are converted to SQL Server using CASE statements.
|
Aggregate Function |
SQL Server Equivalent |
|
Avg |
AVG |
|
Count |
COUNT |
|
Min, Max |
MIN, MAX |
|
StdDev |
STDDEV |
|
StdDevP |
STDVEVP |
|
Sum |
SUM |
|
Var |
VAR |
|
VarP |
VARP |
|
Domain Aggregate Function |
SQL Server Implementation |
|
DAvg |
Sub-Select using AVG(Expr) |
|
DCount |
Sub-Select using COUNT(Expr) |
|
DLookup |
Sub-Select |
|
DFirst, DLast |
Not supported |
|
DMin, DMax |
Sub-Select using MIN(Expr) or MAX(Expr) |
|
DStdDev |
Sub-Select using STDDEV(Expr) |
|
DStdDevP |
Sub-Select using STDDEVP(Expr) |
|
DSum |
Sub-Select using SUM(Expr) |
|
DVar |
Sub-Select using VAR(Expr) |
|
DVarP |
Sub-Select using VARP(Expr) |