VDM includes functions and operators that can be used to build expressions for calculations, comparisons, date logic, text manipulation, conditional values, and data transformations.
Use this guide to look up available syntax, descriptions, and examples.
Quick Navigation
- Operators
- Date and Time Functions
- Logical Functions
- Math Functions
- String Functions
- Constants
- Common Expression Patterns
Operators
Operators are used to perform calculations, compare values, test conditions, and build expression logic.
Arithmetic Operators
| Operator | Description | Example |
|---|---|---|
+ | Adds numbers or combines strings. | [UnitPrice] + 4 |
- | Subtracts one value from another. | [Price1] - [Price2] |
* | Multiplies values. | [Quantity] * [UnitPrice] |
/ | Divides one value by another. | [Quantity] / 2 |
% | Returns the remainder after division. | [Quantity] % 3 |
You can also use + to combine text:
[FirstName] + ' ' + [LastName]
Comparison Operators
| Operator | Description | Example |
|---|---|---|
= or == | Equal to | [ID] = 11 |
!= | Not equal to | [Country] != 'France' |
< | Less than | [UnitPrice] < 20 |
<= | Less than or equal to | [UnitPrice] <= 20 |
> | Greater than | [UnitPrice] > 30 |
>= | Greater than or equal to | [UnitPrice] >= 30 |
Logical Operators
| Operator | Description | Example |
|---|---|---|
And | Both expressions must be true. | [InStock] And ([ExtendedPrice] > 100) |
Or | At least one expression must be true. | [Country] = 'USA' Or [Country] = 'UK' |
Not or ! | Reverses the result of a Boolean expression. | Not [InStock] |
Set, Pattern, and Range Operators
| Operator | Description | Example |
|---|---|---|
In | Checks whether a value exists within a list. | [Country] In ('USA', 'UK', 'Italy') |
Like | Compares text against a pattern. | [Name] Like 'An%' |
Between | Checks whether a value falls within a range. | [Quantity] Between (10, 20) |
Bitwise Operators
| Operator | Description | Example |
|---|---|---|
| | Bitwise OR. | [Flag1] | [Flag2] |
& | Bitwise AND. | [Flag] & 10 |
^ | Bitwise XOR. | [Flag1] ^ [Flag2] |
Unary Operators
| Operator | Description | Example |
|---|---|---|
+ | Returns the value as positive/as-is. | +[Value] |
- | Returns the negative of a value. | -[Value] |
Null Operator
| Operator | Description | Example |
|---|---|---|
Is Null | Checks whether an expression contains a null value. | [Region] Is Null |
Date and Time Functions
Date and time functions can be used to calculate dates, compare date values, extract parts of a date, and build relative date expressions.
Add to a Date or Time
| Function | Description | Example |
|---|---|---|
AddDays(DateTime, DaysCount) | Adds days to a date. | AddDays([OrderDate], 30) |
AddHours(DateTime, HoursCount) | Adds hours to a date/time value. | AddHours([StartTime], 2) |
AddMinutes(DateTime, MinutesCount) | Adds minutes to a date/time value. | AddMinutes([StartTime], 30) |
AddSeconds(DateTime, SecondsCount) | Adds seconds to a date/time value. | AddSeconds([StartTime], 60) |
AddMilliSeconds(DateTime, MilliSecondsCount) | Adds milliseconds. | AddMilliSeconds([StartTime], 5000) |
AddTicks(DateTime, TicksCount) | Adds ticks. | AddTicks([StartTime], 5000) |
AddMonths(DateTime, MonthsCount) | Adds months to a date. | AddMonths([OrderDate], 1) |
AddYears(DateTime, YearsCount) | Adds years to a date. | AddYears([EndDate], -1) |
AddTimeSpan(DateTime, TimeSpan) | Adds a TimeSpan value. | AddTimeSpan([StartTime], [Duration]) |
Use a negative number to subtract time where supported.
Example:
AddDays([OrderDate], -7)
Calculate Differences Between Dates
| Function | Description | Example |
|---|---|---|
DateDiffDay(StartDate, EndDate) | Difference in full days. | DateDiffDay([StartDate], Now()) |
DateDiffHour(StartDate, EndDate) | Difference in full hours. | DateDiffHour([StartDate], Now()) |
DateDiffMinute(StartDate, EndDate) | Difference in full minutes. | DateDiffMinute([StartDate], Now()) |
DateDiffSecond(StartDate, EndDate) | Difference in full seconds. | DateDiffSecond([StartDate], Now()) |
DateDiffMilliSecond(StartDate, EndDate) | Difference in milliseconds. | DateDiffMilliSecond([StartTime], Now()) |
DateDiffTick(StartDate, EndDate) | Difference in ticks. | DateDiffTick([StartDate], Now()) |
DateDiffMonth(StartDate, EndDate) | Difference in months. | DateDiffMonth([StartDate], Now()) |
DateDiffYear(StartDate, EndDate) | Difference in years. | DateDiffYear([StartDate], Now()) |
Extract Parts of a Date or Time
| Function | Description | Example |
|---|---|---|
GetDate(DateTime) | Returns the date portion. | GetDate([OrderDateTime]) |
GetDay(DateTime) | Returns the day of the month. | GetDay([OrderDate]) |
GetDayOfWeek(DateTime) | Returns the day of the week. | GetDayOfWeek([OrderDate]) |
GetDayOfYear(DateTime) | Returns the day of the year. | GetDayOfYear([OrderDate]) |
GetHour(DateTime) | Returns the hour. | GetHour([StartTime]) |
GetMinute(DateTime) | Returns the minute. | GetMinute([StartTime]) |
GetSecond(DateTime) | Returns the second. | GetSecond([StartTime]) |
GetMilliSecond(DateTime) | Returns the milliseconds. | GetMilliSecond([StartTime]) |
GetTimeOfDay(DateTime) | Returns the time portion in ticks. | GetTimeOfDay([StartTime]) |
GetMonth(DateTime) | Returns the month. | GetMonth([OrderDate]) |
GetYear(DateTime) | Returns the year. | GetYear([OrderDate]) |
Current and Relative Date Functions
| Function | Description |
|---|---|
Now() | Current system date and time. |
Today() | Current date at midnight. |
UtcNow() | Current UTC date and time. |
LocalDateTimeNow() | Current local date and time. |
LocalDateTimeToday() | Current local date. |
LocalDateTimeTomorrow() | Tomorrow. |
LocalDateTimeDayAfterTomorrow() | Day after tomorrow. |
LocalDateTimeYesterday() | Yesterday. |
LocalDateTimeThisWeek() | Start of the current week. |
LocalDateTimeNextWeek() | Start of next week. |
LocalDateTimeLastWeek() | Start of last week. |
LocalDateTimeThisMonth() | Start of the current month. |
LocalDateTimeNextMonth() | Start of next month. |
LocalDateTimeLastMonth() | Start of last month. |
LocalDateTimeThisYear() | Start of the current year. |
LocalDateTimeNextYear() | Start of next year. |
LocalDateTimeLastYear() | Start of last year. |
LocalDateTimeTwoWeeksAway() | Start of two weeks forward. |
LocalDateTimeTwoMonthsAway() | Start of two months forward. |
LocalDateTimeTwoYearsAway() | Start of two years forward. |
LocalDateTimeYearBeforeToday() | Date one year before today. |
These functions can be combined with other date functions.
Example:
AddDays(LocalDateTimeToday(), 5)
Date Comparison Functions
| Function | Description | Example |
|---|---|---|
IsSameDay(DateTime) | Checks whether the value falls on the same day. | IsSameDay([OrderDate]) |
IsThisWeek(DateTime) | Checks whether the date is in the current week. | IsThisWeek([OrderDate]) |
IsThisMonth(DateTime) | Checks whether the date is in the current month. | IsThisMonth([OrderDate]) |
IsThisYear(DateTime) | Checks whether the date is in the current year. | IsThisYear([OrderDate]) |
IsLastMonth(DateTime) | Checks whether the date is in the previous month. | IsLastMonth([OrderDate]) |
IsLastYear(DateTime) | Checks whether the date is in the previous year. | IsLastYear([OrderDate]) |
IsNextMonth(DateTime) | Checks whether the date is in the next month. | IsNextMonth([OrderDate]) |
IsNextYear(DateTime) | Checks whether the date is in the next year. | IsNextYear([OrderDate]) |
IsYearToDate(DateTime) | Checks whether the date falls between January 1 and the current date. | IsYearToDate([OrderDate]) |
InDateRange(Date, FromDate, ToDate) | Checks whether a date falls within a specified range. | InDateRange([OrderDate], #1/1/2026#, #12/31/2026#) |
Month Functions
| Function | Description |
|---|---|
IsJanuary(DateTime) | Checks whether the date is in January. |
IsFebruary(DateTime) | Checks whether the date is in February. |
IsMarch(DateTime) | Checks whether the date is in March. |
IsApril(DateTime) | Checks whether the date is in April. |
IsMay(DateTime) | Checks whether the date is in May. |
IsJune(DateTime) | Checks whether the date is in June. |
IsJuly(DateTime) | Checks whether the date is in July. |
IsAugust(DateTime) | Checks whether the date is in August. |
IsSeptember(DateTime) | Checks whether the date is in September. |
IsOctober(DateTime) | Checks whether the date is in October. |
IsNovember(DateTime) | Checks whether the date is in November. |
IsDecember(DateTime) | Checks whether the date is in December. |
Example:
IsJanuary([OrderDate])
Logical Functions
Logical functions evaluate conditions and return values based on the result.
| Function | Description | Example |
|---|---|---|
Iif(Expression, TruePart, FalsePart) | Returns TruePart when the expression is true; otherwise returns FalsePart. | Iif([Quantity] >= 10, 10, 0) |
IsNull(Value) | Returns true when the value is null. | IsNull([OrderDate]) |
IsNull(Value1, Value2) | Returns Value1 when it is not null; otherwise returns Value2. | IsNull([ShipDate], [RequiredDate]) |
IsNullOrEmpty(String) | Returns true when a string is null or empty. | IsNullOrEmpty([ProductName]) |
Math Functions
Math functions perform numeric calculations and transformations.
| Function | Description | Example |
|---|---|---|
Abs(Value) | Returns the absolute value. | Abs([Balance]) |
Acos(Value) | Returns the arccosine in radians. | Acos([Value]) |
Asin(Value) | Returns the arcsine in radians. | Asin([Value]) |
Atn(Value) | Returns the arctangent in radians. | Atn([Value]) |
Atn2(Value1, Value2) | Returns the arctangent of two values. | Atn2([Value1], [Value2]) |
BigMul(Value1, Value2) | Returns the full 64-bit product of two 32-bit integers. | BigMul([Amount], [Quantity]) |
Ceiling(Value) | Rounds up to the nearest whole number. | Ceiling([Value]) |
Cos(Value) | Returns the cosine of an angle in radians. | Cos([Value]) |
Cosh(Value) | Returns the hyperbolic cosine. | Cosh([Value]) |
Exp(Value) | Returns e raised to the specified power. | Exp([Value]) |
Floor(Value) | Rounds down to the nearest whole number. | Floor([Value]) |
Log(Value) | Returns the natural logarithm. | Log([Value]) |
Log(Value, Base) | Returns the logarithm using the specified base. | Log([Value], 2) |
Log10(Value) | Returns the base-10 logarithm. | Log10([Value]) |
Power(Value, Power) | Raises a value to a specified power. | Power([Value], 3) |
Rnd() | Returns a random number between 0 and 1. | Rnd() * 100 |
Round(Value) | Rounds to the nearest whole number. | Round([Value]) |
Sign(Value) | Returns 1, 0, or -1 based on the sign of the value. | Sign([Value]) |
Sin(Value) | Returns the sine of an angle in radians. | Sin([Value]) |
Sinh(Value) | Returns the hyperbolic sine. | Sinh([Value]) |
Sqr(Value) | Returns the square root. | Sqr([Value]) |
Tan(Value) | Returns the tangent of an angle in radians. | Tan([Value]) |
Tanh(Value) | Returns the hyperbolic tangent. | Tanh([Value]) |
String Functions
String functions can be used to combine, format, search, and modify text.
| Function | Description | Example |
|---|---|---|
Ascii(String) | Returns the ASCII value of the first character. | Ascii('a') |
Char(Number) | Returns the character for an ASCII code. | Char(65) |
CharIndex(String1, String2) | Returns the position of one string inside another. | CharIndex('e', 'example') |
CharIndex(String1, String2, StartLocation) | Searches beginning at the specified position. | CharIndex('e', 'example', 2) |
Concat(String1, ..., StringN) | Combines multiple strings. | Concat([FirstName], ' ', [LastName]) |
Insert(String1, StartPosition, String2) | Inserts text into another string. | Insert([Name], 0, 'ABC-') |
Len(Value) | Returns the length of a string. | Len([Description]) |
Lower(String) | Converts text to lowercase. | Lower([ProductName]) |
PadLeft(String, Length) | Pads the left side with spaces. | PadLeft([Name], 30) |
PadLeft(String, Length, Char) | Pads the left side with a specified character. | PadLeft([Name], 30, '0') |
PadRight(String, Length) | Pads the right side with spaces. | PadRight([Name], 30) |
PadRight(String, Length, Char) | Pads the right side with a specified character. | PadRight([Name], 30, '.') |
Remove(String, StartPosition, Length) | Removes characters from a string. | Remove([Name], 0, 3) |
Replace(String, SubString, NewString) | Replaces one part of a string with another. | Replace([Name], 'Old', 'New') |
Reverse(String) | Reverses a string. | Reverse([Name]) |
Substring(String, StartPosition, Length) | Returns part of a string with a specified length. | Substring([Description], 2, 3) |
Substring(String, StartPosition) | Returns the string beginning at the specified position. | Substring([Description], 2) |
ToStr(Value) | Converts a value to text. | ToStr([ID]) |
Trim(String) | Removes leading and trailing spaces. | Trim([ProductName]) |
Upper(String) | Converts text to uppercase. | Upper([ProductName]) |
Constants
Constants are fixed values used within expressions.
| Constant | Description | Example |
|---|---|---|
| String | Text values are enclosed in apostrophes. | [Country] = 'USA' |
| Apostrophe within text | Use two apostrophes to represent one apostrophe. | [Name] = 'O''Neil' |
| Date/Time | Date values are enclosed in #. | [OrderDate] >= #1/1/2026# |
True | Boolean true value. | [InStock] = True |
False | Boolean false value. | [InStock] = False |
? | Represents a null value. | [Region] != ? |
Common Expression Patterns
The following examples demonstrate how functions and operators can be combined for common reporting and data scenarios.
Return a Default Value When a Field Is Null
IsNull([Region], 'Unknown')
Returns Unknown when Region is null. Otherwise, the existing value is returned.
Check for an Empty Text Value
IsNullOrEmpty([CustomerName])
Returns true when CustomerName is null or contains no text.
Categorize a Numeric Value
Iif([Amount] >= 1000, 'High', 'Standard')
Returns High when the amount is 1,000 or greater. Otherwise, it returns Standard.
Check for Multiple Matching Values
Iif([StatusCode] In (10, 20, 30), 'Included', 'Other')
Checks whether StatusCode matches any value in the list.
Change an Amount for Specific Codes
Iif([StatusCode] In (10, 20, 30), -[Amount], [Amount])
This expression:
- Checks whether
StatusCodematches one of the listed values. - Reverses the sign of
Amountwhen a match is found. - Leaves
Amountunchanged for all other codes.
Always Keep Matching Amounts Negative
Iif([StatusCode] In (10, 20, 30), -Abs([Amount]), [Amount])
Using Abs ensures that matching amounts remain negative even if the original amount was already negative.
Always Return a Positive Value
Abs([Amount])
Returns the absolute value of Amount.
Always Return a Negative Value
-Abs([Amount])
Returns Amount as a negative value regardless of its original sign.
Combine First and Last Name
[FirstName] + ' ' + [LastName]
Combines the two fields with a space between them.
Add Text Before a Field
'Account: ' + [AccountNumber]
Adds a label before the field value.
Replace Text
Replace([Description], 'Old', 'New')
Replaces occurrences of Old with New.
Remove Leading and Trailing Spaces
Trim([CustomerName])
Removes spaces from the beginning and end of a text value.
Convert Text to Uppercase
Upper([CustomerName])
Converts the value to uppercase.
Find Text That Starts With a Specific Pattern
[CustomerName] Like 'A%'
Matches values beginning with the letter A.
Check Whether a Value Falls Within a Range
[Amount] Between (100, 500)
Returns true when Amount falls within the specified range.
Calculate Days Between Two Dates
DateDiffDay([StartDate], [EndDate])
Returns the number of full days between the two dates.
Add Days to a Date
AddDays([DueDate], 30)
Returns a date 30 days after DueDate.
Calculate a Date in the Past
AddDays([OrderDate], -30)
Returns a date 30 days before OrderDate.
Extract a Year From a Date
GetYear([OrderDate])
Returns the year portion of OrderDate.
Check Whether a Date Is in the Current Month
IsThisMonth([OrderDate])
Returns true when OrderDate falls within the current month.
Combine Multiple Conditions
Iif([Amount] > 500 And [Status] = 'Open', 'Review', 'No Review')
Returns Review only when both conditions are true.
Use Either of Two Conditions
Iif([Status] = 'Open' Or [Status] = 'Pending', 'Active', 'Closed')
Returns Active when either condition is true.
Combine Null Handling With Text
'Region: ' + IsNull([Region], 'Unknown')
Adds a label while also providing a fallback when the field is null.
Comments
0 comments
Please sign in to leave a comment.