Visual Basic Access (VBA) built-in functions include some ANSI standard functions that have similar Oracle equivalents, like Asc and Len, and some non-standard functions like Format, IIf, and DatePart that have no direct Oracle equivalents, and some platform dependent functions, like Environ and CodeDB that are not applicable to Oracle.
Standard built-in functions are implemented by substituting the Oracle verb for the Access one, ex. Len is replaced by LENGTH. Many non-standard Access functions, like DateAdd, DateDiff, and Format are implemented by Scriptoria by replacing the built-in functions with inline Oracle equivalent constructs. Scriptoria includes a package (VBA_FUNCTIONS) that implements some non-standard VBA functions using Oracle PL*SQL functions. This includes MS-Access built-in functions, like NZ and Right that require overloading to support the MS-Access Variant type, and DateDiff and DatePart functions that include the FirstDayOfWeek optional parameter. Incompatible functions are ignored.
|
VB Function |
Oracle Equivalent |
Implementation |
|
& |
“||” |
Inline |
|
Asc |
ASCII |
Inline |
|
AscB |
|
not available (n/a) |
|
AscW |
|
n/a |
|
Atn |
ATAN |
Inline |
|
CBool |
|
n/a |
|
CByte |
TO_NUMBER |
Inline |
|
CCur |
TO_NUMBER |
Inline |
|
CDate |
|
inline |
|
CDbl |
TO_NUMBER |
Inline |
|
CDec |
TO_NUMBER |
Inline |
|
Chr |
CHR |
Inline |
|
Chr$ |
CHR |
Inline |
|
ChrB |
|
n/a |
|
ChrB$ |
|
n/a |
|
ChrW |
|
n/a |
|
ChrW$ |
|
n/a |
|
CInt |
TO_NUMBER |
Inline |
|
CLng |
TO_NUMBER |
Inline |
|
CSng |
TO_NUMBER |
Inline |
|
CStr |
TO_CHAR |
Inline |
|
CVDate |
|
Inline |
|
Date |
SysDate |
Inline |
|
Date$ |
SysDate |
Inline |
|
DateAdd |
|
Inline |
|
DateDiff |
|
Inline and PL*SQL UDF |
|
DatePart |
|
Inline and PL*SQL UDF |
|
DateSerial |
|
PL*SQL UDF |
|
DateValue |
|
PL*SQL UDF |
|
Day |
|
inline |
|
Fix |
INT(TRUNC(expr)) |
Inline |
|
Format |
|
inline (See Format) |
|
Format$ |
|
|
|
Hex |
HEX |
Inline |
|
Hex$ |
HEX |
Inline |
|
Hour |
|
inline |
|
IIf |
|
|
|
InStr |
|
PL*SQL UDF |
|
InStrB |
|
PL*SQL UDF |
|
Int |
FLOOR |
Inline |
|
IsDate |
ISDATE |
n/a |
|
IsNull |
ISNULL |
Inline |
|
IsNumeric |
ISNUMERIC |
n/a |
|
LCase$ |
LCASE |
Inline |
|
Left |
|
PL*SQL UDF |
|
Left$ |
|
PL*SQL UDF |
|
LeftB |
|
n/a |
|
LeftB$ |
|
n/a |
|
Len |
LENGTH |
Inline |
|
LenB |
|
Inline |
|
LTrim$ |
LTRIM |
Inline |
|
Mid |
SUBSTR |
PL*SQL UDF |
|
Mid$ |
SUBSTR |
PL*SQL UDF |
|
MidB |
|
PL*SQL UDF |
|
MidB$ |
|
PL*SQL UDF |
|
Month |
|
inline |
|
MonthName |
|
PL*SQL UDF |
|
Now |
SysDate |
Inline |
|
Nz |
|
PL*SQL UDF |
|
Oct |
|
n/a |
|
Oct$ |
|
n/a |
|
Right |
|
PL*SQL UDF |
|
Right$ |
|
PL*SQL UDF |
|
RightB |
|
n/a |
|
RightB$ |
|
n/a |
|
Rnd |
RAND |
Inline |
|
RTrim$ |
RTRIM |
Inline |
|
Second |
|
inline |
|
Sgn |
SIGN |
Inline |
|
Space |
SPACES |
Inline |
|
Space$ |
SPACES |
Inline |
|
Sqr |
SQRT |
Inline |
|
Str |
TO_CHAR |
Inline |
|
Str$ |
TO_CHAR |
Inline |
|
StrConv |
|
n/a |
|
String |
REPLICATE |
inline |
|
String$ |
REPLICATE |
inline |
|
Switch |
|
|
|
Time |
SysDate |
Inline |
|
Time$ |
SysDate |
Inline |
|
TimeSerial |
|
PL*SQL UDF |
|
TimeValue |
TIME |
n/a |
|
Trim |
|
inline |
|
Trim$ |
|
inline |
|
UCase$ |
UCASE |
Inline |
|
Val |
|
PL*SQL UDF |
|
Weekday |
DAYOFWEEK |
inline |
|
WeekdayName |
|
PL*SQL UDF |
|
Year |
|
inline |
Note-1: The functions contained in the VBA_FUNCTIONS package are overloaded to implement various options and data types supported by the associated VBA functions.
Note-2: The FirstDayOfWeek parameter is supported, but the FirstDayOfYear is not since it does not affect any implemented functions.
VBA "IIf()" or "Switch()" functions are converted to Oracle using the CASE statement.
|
Aggregate Function |
Oracle Equivalent |
|
Avg |
AVG |
|
Count |
COUNT |
|
Min, Max |
MIN, MAX |
|
StdDev |
STDDEV |
|
StdDevP |
Not supported |
|
Sum |
SUM |
|
Var |
VARIANCE |
|
VarP |
Not Supported |
|
Domain Aggregate Function |
Oracle 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 |
Not supported |
|
DSum |
Sub-Select using SUM(Expr) |
|
DVar |
Sub-Select using VARIANCE(Expr) |
|
DVarP |
Not Supported |
The inline Format conversion supports all Access named and most user-defined format masks. For example, Format (Now(),"general date") and Format (Date(),"d/MM/yyyy") are supported. The inline functions are internationally aware. Regional settings are retrieved from the Windows PC performing the conversion.
|
Format Function Signature |
Description |
|
Format(Number,"General Number") |
Display number with no thousand separator. |
|
Format(Number,"Currency") |
See Access documentation |
|
Format(Number,"Fixed") |
See Access documentation |
|
Format(Number,"Standard") |
See Access documentation |