The following functions are available when you type both conditions and actions for validation rules. This means that you can use these to type either an expression to be evaluated or a value to be assigned.
|
|
DatesFrom a date, get the day, the month, or the year |
StringsGet the number of characters in a string Delete spaces from the beginning or end of a string Extract part of a string: from the right, from the left, or specific positions Convert uppercase characters to lowercase or vice versa |
NumbersMake a negative number positive Round a decimal number up or down Get the greater or lesser of two values Get the numeric representation of an integer or string |
FieldsCheck whether a field is filled in Check whether all values found in a series of indexed fields are unique |
Abs(Number) – Makes a negative number positive.
Examples: Abs(-10) = 10; Abs("-1.456") = 1.456; Abs(1) = 1.
Ceiling(Number) – Rounds a decimal number to the closest greater integer.
Examples: Ceiling(1.456) = 2; Ceiling(5.867) = 6
Date() or Date(Field) or Date(String) – Allows you to compare two dates. When the parentheses are empty, the current date is compared.
Always use the Date function when comparing dates.
The function primarily looks for a date of the format set in your Windows settings.
Note that if either of the dates in the comparison is invalid, the comparison is false.
Example: Date(Field) = Date() returns true if the date in Field matches today’s date.
Day(Date) – Returns the day in month of Date. The function looks primarily for a date in the format specified by your Windows settings.
Example: Day("1/25/00") returns 25
Days(Date1, Date2) – Returns the number of days between Date1 and Date2. The function looks primarily for a date in the format specified by your Windows settings.
Examples:
Days("10/30/2000","01/01/01") returns 63
Days("01/01/01", "10/30/2000") returns 63
Days("10/30/2000","10/30/2000") returns 0
Floor(Number) – Rounds a decimal number to the closest lower integer.
Examples: Floor(1.456) = 1; Floor(5.867) = 5
Int(Floating point number) – Returns the integer representation of a floating point number (same as floor).
Example: Int(1.7345) = 1
IsFilledIn(Field) – Checks if a field is filled in on a form.
Example: IsFilledIn(Field) = true if the field is filled in on the form.
IsNumeric(String) – Checks if a string or field is a number.
Example: IsNumeric("1.456") = Not IsNumeric("FreeThrow")
IsUnique(Field) – Checks whether all values found in a series of indexed fields are unique. If even one value is repeated within the series, the function returns false.
Example: If Field has indexed fields from 1 to 4 with values Field[1] = 10, Field[2] = 5, Field[3] = 20, Field[4] = 30, then IsUnique(Field) = true.
Example: If Field has indexed fields from 1 to 5 with values Field[1] = 10, Field[2] = 5, Field[3] = 20, Field[4] = 30, and Field [5] = 5, then IsUnique(Field) = false.
IsValidDate(Date) or IsValidDate(String) – Checks whether Date is a valid date. The function expects a date in the short date format specified in your Windows settings.
Examples: If your Windows setting is YYYY-MM-DD, then:
IsValidDate("2000-10-30") is true
IsValidDate("00-10-30") is false
IsValidDate("2000/10/30") is false
Left(String, Length) – Extracts the left part of a string.
Example: Left("Layup", 3) = "Lay"
Len(String) – Returns the number of characters in a string.
Example: Len("Floater") = 7
LowerCase(String) - Converts all uppercase characters to lowercase.
Example: LowerCase("SlamDunk") = "slamdunk"
Max(Value1, Value2) – Returns the greater of the two values.
Example: Max(1,3) = 3
Mid(String, From, Length) – Extracts part of a string.
Example: Mid("Layup", 3,2) = "yu"
Min(Value1, Value2) – Returns the lesser of the two values.
Example: Min(1,3) = 1
Month(Date) - Returns the month that Date is in. The function looks primarily for a date in the format specified by your Windows settings.
Example: Month("1/25/00") returns 1
Right(String, Length) – Extracts the right part of a string.
Example: Right("Layup", 3) = "yup"
Sum(Field) – Calculates the sum of the indexed fields Field.
Sum(Field, From, To) - Calculates the sum of the fields between the given indexes.
Example: If Field has indexed fields from 1 to 4 with values Field[1] = 10, Field[2] = 5, Field[3] = 20, Field[4] = 30, then Sum(Field) = 65 and Sum(Field, 2, 3) = 25.
TrimLeft(String) – Deletes spaces at the beginning of a string.
Example: TrimLeft(" Basketball") = "Basketball"
TrimRight(String) – Deletes spaces at the end of a string.
Example: TrimRight("Basketball ") = "Basketball"
UpperCase(String) - Converts all lowercase characters to uppercase.
Example: UpperCase("SlamDunk") = "SLAMDUNK"
Val(String) – Returns the numeric representation of a string. If the string does not match a numeric representation, the function returns 0.
Examples: Val("1.234") = 1.234; Val("1") = 1.0
Year(Date) – Returns the year, including the century, from Date. The function looks primarily for a date in the format specified by your Windows settings.
Examples: Year("1/25/2001") = 2001; Year("1/25/01") = 2001
Years(Date1, Date2) – Returns the number of full years between Date1 and Date2. The order of the dates does not matter. The function looks primarily for a date in the format specified by your Windows settings.
Examples: Years("1/12/99", "1/11/2003") = 3
Years("1/12/99", "1/12/2003") = 4
Years("6/2/03", "5/12/97") = 5