The following lists the ANSI standard ODBC scalar functions and their equivalents for VBA-based data sources and SQL Server

ANSI | VBA | SQL | Description |
---|---|---|---|

Abs | Abs | Abs | absolute value |

Acos | Acos | arc cosine | |

Ascii | Asc | Ascii | converts a single char to an ASCII numeric value |

Asin | Asin | arc sine | |

Atan | Atn | Atan | arc tangent of angle |

Atan2 | Atn2 | arc tangent of two values | |

Bit_Length | length in bits of binary value | ||

Cast | Cast | general type conversion | |

Ceiling | Ceiling | least integer greater than or equal | |

Char | Chr | Char | converts an ASCII numeric value to a single char |

Char_Length | Len | Length | length in characters of string |

Concat | & | + | concatenate two strings |

Convert | Convert | general type conversion | |

Cos | Cos | Cos | cosine of angle |

Cot | Cot | cotangent of angle | |

Current_Date | Date | current date | |

Current_Time | Time | current time | |

Current_Timestamp | Now | GetDate | current date and time |

Database | DB_Name | database name | |

DayName | name of day of week | ||

DayOfMonth | Day | Day | numeric day of month |

DayOfWeek | Weekday | numeric day of week | |

DayOfYear | numeric day of year | ||

Degrees | Degrees | converts an angle in radians to degrees | |

Difference | Difference | fuzzy string comparison | |

Exp | Exp | Exp | natural exponentiation |

Extract | extract field from timestamp | ||

Floor | Int | Floor | greatest integer less than or equal |

Hour | Hour | numeric hour of day | |

IfNull | IsNull | returns first non-null value | |

Insert | |||

LCase | LCase | Lower | lowercase string |

Left | Left | Left | substring from left side of a string |

Length | length in bytes of right-trimmed string | ||

Locate | InStr | CharIndex | searches for a substring |

Log | Log | Log | natural logarithm |

Log10 | Log10 | base 10 logarithm | |

LTrim | LTrim | LTrim | trims whitespace from left side of string |

Minute | Minute | numeric minute of hour | |

Mod | Mod | % | modulo of x base y |

Month | Month | Month | numeric month value |

MonthName | name of month | ||

Octet_Length | length in octets of value | ||

Pi | Pi | value of pi | |

Position | |||

Power | ^ | Power | exponentiation |

Quarter | numeric quarter of year | ||

Radians | Radians | converts an angle in degrees into radians | |

Rand | Rnd | random value | |

Repeat | String | Replicate | generates n repeated characters |

Replace | string replacement | ||

Right | Right | substring from right part of string | |

Round | Round | Round | round to an integer value |

RTrim | RTrim | RTrim | trims whitespace from right side of string |

Second | Second | numeric second value | |

Sign | Sgn | Sign | arithmetic sign of value |

Sin | Sin | Sin | sine |

SoundEx | SoundEx | generates hash of text string | |

Space | Space | Space | generate n spaces |

Sqrt | Sqr | Sqrt | square root |

SubString | Mid | SubString | middle part of string extraction |

Tan | Tan | Tan | tangent of angle |

TimestampDiff | difference between two timestamps | ||

UCase | UCase | Upper | uppercase string |

User | User_Name | user name | |

Week | numeric week of year | ||

Year | Year | Year | numeric year value |