QuickModules.com

Back Contents Print Close

Functions and Formulas
Examples See also

Description
Formulas are useful for modifying values through calculations and/or functions. QuickModules.com supports formulas and allows you to return values calculated by formulas. Some places formulas are used are formula fields in tables, update values in an update procedure, and search conditions in a search procedure.

Types of Formulas
QuickModules.com supports three types of formulas: numeric formulas, text formulas, and date formulas.

Formula Syntax
A formula consists of one or more elements separated by operators. An element is one of the following:

Number: 5, 100.4, 30.54
Text: 'Joe', 'The capital of Canada is Ottawa'
Variable: [DATE], [CUSTOMER.ID], [VAR.ORDER_ID]
Function: Upper( ... ), Year( ... ), Length( ... )

The following operators are supported:

+: addition for numbers, concatenation of strings, date addition for dates
-: subtration for numbers, date subtraction for dates
*: multiplication for numbers
/: division for numbers
%: modulo (division remainder) for numbers

Parenthesis can be used to group parts of a formula.

Functions
The following built-in functions are supported by QuickModules.com:

Type	Function	Description	Example
Text	Left	Left part of a string	Left('Joe Smith', 3) = 'Joe'
Text	Right	Right part of a string	Right('Joe Smith', 5) = 'Smith'
Text	Lowercase	Converts a string to lower case	Lowercase('Joe') = 'joe'
Text	Uppercase	Converts a string to upper case	Uppercase('Joe') = 'JOE'
Text	Length	Returns the length of a string	Length('Joe') = 3
Text	Trim	Removes trailing spaces	Trim(' Joe ') = 'Joe'
Text	ToText	Converts a value to a string	ToText(3) = '3'
Number	Pos	Returns the position of one string in another	Pos('Smith', 'Joe Smith') = 5
Number	Abs	Returns the absolute value of a number	Abs(-3) = 3
Number	Floor	Returns the integer part of a number	Floor(3.6) = 3
Number	Ln	Returns the natural log of a number	Ln(10)
Number	Log	Returns the log base n of a number	Log(16,2)
Number	Round	Rounds a number to the nearest integer	Round(3.6) = 4
Number	Sqrt	Returns the square root of a number	Sqrt(30)
Number	ToNum	Converts a value to a number	ToNum('3') = 3
Number	Day	Returns the day of the month from a date	Day('01-JAN-2001')
Number	Month	Returns the month number of a date	Month('01-JAN-2001')
Number	Year	Returns the year of a date	Year('01-JAN-2001')
Number	DayDiff	Returns the difference in days between 2 dates	DayDiff([DATE], '01-JAN-2001')
Number	MonthDiff	Returns the difference in months between 2 dates	MonthDiff([DATE], '01-JAN-2001')
Number	YearDiff	Returns the difference in years between 2 dates	YearDiff([DATE], '01-JAN-2001')
Number	SecondDiff	Returns the difference in seconds between 2 dates	SecondDiff([DATE], '01-JAN-2001')
Number	MinuteDiff	Returns the difference in minutes between 2 dates	MinuteDiff([DATE], '01-JAN-2001')
Number	HourDiff	Returns the difference in hours between 2 dates	HourDiff([DATE], '01-JAN-2001')
Date	AddDays	Adds a number of days to a date. Use a negative number of days to subtract days	AddDays('01-JAN-2001', 1)
Date	AddMonths	Adds a number of months to a date. Use a negative number of months to subtract months	AddMonths('01-JAN-2001', 1)
Date	AddSeconds	Adds a number of seconds to a date. Use a negative number ofseconds to subtract seconds	AddSeconds([DATE], 43)
Date	AddMinutes	Adds a number of minutess to a date. Use a negative number of minutes to subtract minutes	AddMinutes([DATE], 32)
Date	AddHours	Adds a number of hours to a date. Use a negative number of hours to subtract hours	AddHours([DATE], 5)
Date	RemoveTime	Removes the time part from a date value. Like setting the clock for that date to midnight.	RemoveTime([DATE])
Date	ToDate	Converts a value to a date	ToDate('01-JAN' + '-2000')
All	Max	Returns the highest of 2 values	Max(23, 45) = 45
All	Min	Returns the lowest of 2 values	Min(23, 45) = 23

Example
The following are examples of valid formulas:

Formula	Description
4 * (5 -3)	Arithmetic formula
YearDiff([DATE], [Person.Birthdate])	Calculates the age from the Birthdate field in the Person table
Uppercase([First Name] + ' ' + [Last Name])	Constructs an uppercase full name from the first name and last name

See Also
Variables