A function can be used as part of an expression. Functions are specified as the function name (case is unimportant) followed by left and right parentheses. Parameters (values for the function to work on) may be specified inside the parentheses, and may be constant values (such as "Hello" or the number 10), fields from a database (such as CUSTOMERS.COMPANY), or another expression (such as YEAR(DATE())). You can surround strings with either single (') or double (") quotes.

There are hundreds of functions built into Stonefield Query. However, most of them are relatively obscure or aren't relevant to querying. Here is a list of the more useful functions.

AccpacDate( DATE_VALUE )
Converts a date value into an Sage 300 date, which is stored as a numeric value such as 20090630 for 06/30/2009.

If today is 06/30/2009, AccpacDate(DATE()) returns 20090630.

AccpacDateToRealDate( VALUE )
Converts an Sage 300 date, which is stored as a numeric value such as 20090630 for 06/30/2009, to a real date value.

If AROBL.DATEDUE contains 20090630, AccpacDateToRealDate(AROBL.DATEDUE) returns 06/30/2009.

ALLTRIM( CHAR_VALUE )
Removes all blanks from both the beginning and the end of the parameter.

ALLTRIM(" This is a test ") returns "This is a test"

AT( CHAR_VALUE_TO_FIND, CHAR_VALUE_TO_SEARCH_IN )
ATC( CHAR_VALUE_TO_FIND, CHAR_VALUE_TO_SEARCH_IN )
Returns the position at which CHAR_VALUE_TO_FIND is found within CHAR_VALUE_TO_SEARCH_IN or 0 if CHAR_VALUE_TO_FIND is not found in CHAR_VALUE_TO_SEARCH_IN. With AT(), the search is case-sensitive; use ATC() for a case-insensitive search.

AT("soft", "Microsoft") returns 6, AT("soft", "MicroSoft") returns 0 (because of the capital "S"), and ATC("soft", "MicroSoft") returns 6.

BETWEEN( VALUE, START, END )
Returns True if the VALUE parameter is between the START and END parameters.

BETWEEN(10, 5, 20) returns True and BETWEEN(10, 15, 20) returns False.

BusinessDays( START_DATE, END_DATE ) Returns the number of business days between the two specified dates. It skips Saturday and Sunday. It can also optionally skip holidays. To specify which dates are holidays, create a file named Holidays.txt in the program folder and put the date for each holiday on its own line; for example:

01/01/2018
12/25/2018

If StartDate is 05/25/2018 (a Friday), EndDate is 05/29/2018 (the following Tuesday), and Holidays.txt contains 05/28/2018 (Memorial Day in the U.S.), BusinessDays(StartDate, EndDate) returns 1, since 05/29/2018 is the next business day after 05/25/2018.

CDOW( DATE_VALUE )
Returns the day part of the date parameter as the spelled-out day. "CDOW" is an abbreviation for "character day of week."

If SomeDate is 03/30/2004, CDOW(SomeDate) returns "Tuesday"

CHR( NUMERIC_VALUE )
Returns the character whose numeric ASCII code is the parameter. The parameter must be between 0 and 255.

CHR(65) returns "A"

CMONTH( DATE_VALUE )
Returns the month part of the date parameter as the spelled-out month. "CMONTH" is an abbreviation for "character month."

If SomeDate is 03/30/2004, CMONTH(SomeDate) returns "March"

CTOD( CHAR_VALUE )
Converts a character value into a date value. "CTOD" is an abbreviation for "character to date conversion."

CTOD("03/15/2004") returns 03/15/2004 as a date

DATE()
Returns the current system date.

DATETIME()
Returns the current system date/time.

DATE( YEAR, MONTH, DAY )
Converts the year, month, and day (which are specified as numeric values) into a date value.

DATE(2004, 3, 30) returns 03/30/2004 as a date

DATETIME( YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS )
Converts the year, month, day, hour, minute, and seconds (which are specified as numeric values, with hour being a 24-hour value) into a date/time value.

DATETIME(2004, 3, 30, 23, 55, 0) returns 03/30/2004 11:55:00 PM as a date/time value.

DAY( DATE_VALUE )
Returns the day part of the date parameter as a numeric value from 1 to 31.

If SomeDate is 03/30/2004, DAY(SomeDate) returns 30.

DOW( DATE_VALUE, START )
Returns the day part of the date parameter as a numeric value representing the day of the week (1 for Sunday through 7 for Saturday). "DOW" is an abbreviation for "day of week."

If SomeDate is 03/30/2004, DOW(SomeDate) returns 3 (the third day of the week, which is Tuesday).

If the first day of the week isn't Sunday, you can tell this function which day you've specified as the first day by using oApp.nFirstDay for the second parameter.

If SomeDate is 03/30/2004 and you've specified Monday as the first day of the week, DOW(SomeDate, oApp.nFirstDay) returns 2 (the second day of the week, which is Tuesday).

DTOC( DATE_VALUE )
Converts a date value into a character value. "DTOC" is an abbreviation for "date to character conversion."

If SomeDate is 03/30/2004, DTOC(SomeDate) returns "03/30/2004"

DTOS( DATE_VALUE )
Converts a date value into a character value formatted as "YYYYMMDD". "DTOS" is an abbreviation for "date to string conversion."

If SomeDate is 03/30/2004, DTOS(SomeDate) returns "20040330"

EMPTY( VALUE )
Returns True if the parameter is an empty string or a string containing only spaces, is a numeric parameter with a value of 0, or is an empty date value.

If Address1 is an empty string, EMPTY(Address1) returns True.

Format( FORMAT_STRING, VALUE1, VALUE2, ... )
Combining character and non-characters values can be some work, because you have to convert the non-character values using functions like TRANSFORM() or DTOC(). The Format function makes it easy to do this; it converts the values to strings based on the formats specified and inserts them into another string.

FORMAT_STRING uses placeholders to indicate where the values are to be inserted into the string and how they are to be converted. A placeholder is a numeric value indicating which value to insert surrounded by < and > and optionally a format code. For example, in the expression Format("You owe <0> as of <1>", 100, DATE()), "<0>" is a placeholder for the first value (100) and "<1>" is a placeholder for the second (the current date). The result of this expression is something like "You owe 100 as of 06/07/2016".

A placeholder can include a format string indicating how to convert the value; the format string is specified as a colon following the value number, then one or more characters indicating the format. For example, the format string "D" tells the Format function to convert a date value to a long date format, so Format("Today is <0:d>", DATE()) gives something like "Today is June 7, 2016".

The format strings for numeric values are:

SymbolDescription
cDisplays the value using the currency settings specified in the Regional Settings applet of the Windows Control Panel (for example, "$" as the currency symbol, "," as the thousands separator, and "." as the decimal separator in North America) and two decimal places. For example, 4132 formatted as c displays as $4,132.00 in North America and €4.132,00 in Germany.
cnLike c but uses the number of decimal places specified as n. For example, 4132 formatted as c3 displays as $4,132.000 in North America and €4.132,000 in Germany.
nLike c but doesn't include the currency symbol. For example, 4132 formatted as n displays as 4,132.00 in North America and 4.132,00 in Germany.
nnLike n but uses the number of decimal places specified as n. For example, 4132 formatted as n3 displays as 4,132.000 in North America and 4.132,000 in Germany.
dDisplays the value as a whole number. For example, 4132.67 formatted as d displays as 4132.
dnLike d but left pads the number to the number of places specified as n using zeros. For example, 4132 formatted as d8 displays as 00004132.
fLike c but doesn't include the currency symbol, thousands separators, or decimal places. For example, 4132.67 formatted as f displays as 4132.
fnLike f but uses the number of decimal places specified as n. For example, 4132 formatted as f3 displays as 4132.000 in North America and 4132,000 in Germany.
pMultiplies the value by 100 and displays it as a percentage to two decimal places. For example, 0.678 formatted as p displays as 67.80%.
pnLike p but uses the number of decimal places specified as n. For example, 0.678 formatted as p0 displays as 68%.

You can also create a custom format string using "9" as a placeholder for a digit, "," as placeholder for the thousands separator symbol specified in the Regional Settings applet of the Windows Control Panel (for example, "," in North America), and "." as a placeholder for the decimal separator symbol specified in the Regional Settings applet (for example, "." in North America). For example, Format("<0:9,999.99>", 4132) displays as 4,132.00 in North America and 4.132,00 in Germany.

The format strings for date and datetime values are:

SymbolDescription
dDisplays a datetime value using the format MM/dd/yyyy. For example, January 10, 2013 2:22:30 PM displays as 01/10/2013.
DDisplays a datetime value using the format MMMM d, yyyy. For example, January 10, 2013 2:22:30 PM displays as January 10, 2013.
fDisplays a datetime value using the format MMMM d, yyyy hh:mm tt. For example, January 10, 2013 2:22:30 PM displays as January 10, 2013 2:22 PM.
FLike f but includes seconds. For example, January 10, 2013 2:22:30 PM displays as January 10, 2013 2:22:30 PM.
gDisplays a datetime value using the format dd/MM/yyyy hh:mm tt. For example, January 10, 2013 2:22:30 PM displays as 10/01/2013 2:22 PM.
GLike g but includes seconds. For example, January 10, 2013 2:22:30 PM displays as 10/01/2013 2:22:30 PM.
MDisplays a datetime value using the format MMMM d. For example, January 10, 2013 2:22:30 PM displays as January 10.
rDisplays a datetime value in GMT using the format ddd, d MMM yyyy hh:mm:ss GMT. For example, January 10, 2013 2:22:30 PM displays as Wed, 10 Jan 2013 8:22:00 GMT.
tDisplays a datetime value using the format hh:mm tt. For example, January 10, 2013 2:22:30 PM displays as 2:22 PM
TLike t but includes seconds. For example, January 10, 2013 2:22:30 PM displays as 2:22:30 PM

You can also create a custom format string using the following characters:

SymbolDescription
hhA placeholder for hours.
mmA placeholder for minutes.
ssA placeholder for seconds.
ttA placeholder for AM or PM.
MMMMA placeholder for a fully spelled-out month, such as December.
MMMA placeholder for an abbreviated spelled-out month, such as Dec.
MMA placeholder for a month number.
ddddA placeholder for a fully spelled-out day, such as Friday.
dddA placeholder for an abbvreviated spelled-out day, such as Fri.
ddA placeholder for a day number with two digits, such as 07.
dA placeholder for a day number with only as many digits as necessary, such as 7.
yyyyA placeholder for a year with century.
yyA placeholder for a year without century.

For example, Format("<0:dd/MM/yyyy hh:mm tt>", DATE()) displays something like 10/01/2013 2:22 PM.

GetCompany()
Gets the company name for the current open Sage 300 database.

GetCompanyField( FIELDNAME )
Gets the specified field from the Sage 300 company table (CSCOM) for the current open Sage 300 database

GetCompanyField('CONAME') returns the company name.

GetConditionValue( FIELDNAME, VALUENUMBER, INSTANCE )
Returns the value of the specified filter condition for the specified field. For "is between" or "is one of" conditions, which have more than one value, specify a value number.

If a report is filtered on Customers.Country equals Great Britain, "Sales for " + GetConditionValue('Customers.Country') returns "Sales for Great Britain". If the report is filtered on Customers.Country is one of Great Britain, Norway, "Sales for " + GetConditionValue('Customers.Country', 2) returns "Sales for Norway".

If there are two conditions for Customers.CompanyName, use GetConditionValue('Customers.CompanyName', 1, 2) to get the value of the second condition.

If the user choses Ignore this condition, the value returned is null.

GetGLBudgetForPeriod( ACCOUNTID, FISCALYEAR, PERIOD )
This function finds the GL budget amount (budget 1 only) for a specific account, fiscal year, and period. It can be used in a formula, for example, to get the budget amount for an account in a transaction summary report, showing budget and actuals.

GetValueForField( FIELDNAME, OPERATOR, DEFAULT, INSTANCE )
Prompts the user for the value for a field. This is similar to the ask-at-runtime condition dialog except the value isn't used in a filter condition: it's used anywhere you wish in a report. If you call the function more than once with the same field name, the user isn't prompted again; instead, the value they entered the first time is returned. If OPERATOR isn't specified, "equals" is assumed. If the function returns NULL, the user clicked cancel.

For OPERATOR, use one of the following:

  • equals
  • not equals
  • is not equal
  • begins with
  • not begins with
  • does not begin with
  • contains
  • not contains
  • does not contain
  • greater than
  • is greater than
  • greater than or equal
  • is greater than or equal
  • less than
  • is less than
  • less than or equal
  • is less than or equal
  • between
  • is between
  • not between
  • is not between
  • not is between
  • is one of
  • is not one of
  • not is one of
  • is blank
  • is not blank
  • not is blank
  • is known
  • is unknown
  • is yes
  • is no

If DEFAULT isn't specified, the default for the value is empty. If you want a default value specified, pass the desired value. For example:

lnMonth = GetValueForField('ORDERS.ORDERDATE', 'equals', date())

If you're using the "between" or "one of" operators, pass a collection of values. For example:

loValues = SQApplication.GetValuesCollection()
loValues.Add('1', date())
loValues.Add('2', date() + 7)
lnMonth = GetValueForField('ORDERS.ORDERDATE', 'between', loValues)

This specifies that the default for the starting date should be today and today plus seven days for the ending date. Note that this can only be used in code, such as the AfterData script for a report, and not in an expression. To do this in an expression, use the ValuesCollection function, passing the values as parameters:

GetValueForField('ORDERS.ORDERDATE', 'between', 
    ValuesCollection(date(), date() + 7))

For example, in an advanced layout report, you might want a field that sums values only for a certain date. To ask the user for the value to use for the date, call GetValueForField('Table.Field'). You can use the return value both in the column header and in the field in the detail band, but the user will only be prompted once for the value.

If the operator is "between" or "one of", specify which of the values you want with the INSTANCE parameter. For example, this returns the second date entered:

GetValueForField('ORDERS.ORDERDATE', 'between', null, 2)

GetValueForParameter( CAPTION, DATATYPE, LENGTH, DECIMALS, OPERATOR, DEFAULT, INSTANCE, CHOICES, FORMAT )
Prompts the user for a value of the specified data type. This is similar to GetValueForField except rather than specifying a field, you indicate the caption, data type, etc. If OPERATOR isn't specified, "equals" is assumed. DATATYPE should be "C" for character, "N" for numeric, or "D" for date. LENGTH is the maximum length of the value and DECIMALS is the number of decimals (only necessary if DATATYPE is "N").

If you want to provide a list of choices for the user to choose from, specify a collection of those choices using the ValuesCollection function:

GetValueForParameter('City', 'C', 30, 0, 
    'equals', .F., 1, 
    ValuesCollection('New York', 'Chicago', 'Miami'))

The FORMAT parameter allows you to specify an input format. The most common value is "@!" to force the entry to upper case.

GOMONTH( DATE_VALUE, NUMBER_OF_MONTHS )
Returns a date the specified number of months from the date parameter. If NUMBER_OF_MONTHS is a positive value, a date after the specified date is returned; use a negative value to obtain a date before the specified date.

If SomeDate is 03/31/2004, GOMONTH(SomeDate, -1) returns 02/29/2004 and GOMONTH(SomeDate, 1) returns 04/30/2004

GoMonthDay( DATE_VALUE, NUMBER_OF_MONTHS, DAY )
Similar to GOMONTH() but returns a date of the specified day the specified number of months from the date parameter. If NUMBER_OF_MONTHS is a positive value, a date after the specified date is returned; use a negative value to obtain a date before the specified date. If DAY is greater than the number of days in the new month (for example, if the current date is in March and NUMBER_OF_MONTHS is 1, which gives April, and DAY is 31), the last day of the month is returned (in this example, April 30).

If SomeDate is 03/31/2004, GoMonthDay(SomeDate, -1, 15) returns 02/15/2004 and GoMonthDay(SomeDate, 1, 31) returns 04/30/2004

HOUR( DATETIME_VALUE )
Returns the hour portion of the specified date/time value in 24-hour format.

If SomeDate is 03/31/2004 11:55:00 PM, HOUR(SomeDate) returns 23.

ICASE( EXPRESSION1, VALUE1, EXPRESSION2, VALUE2, ... EXPRESSIONn, VALUEn, OTHERWISEVALUE )
If EXPRESSION1 is True, ICASE (which stands for "Immediate CASE") returns VALUE1. Otherwise, it evaluates EXPRESSION2, and returns VALUE2 if it's True. The comparison continues until either one of the expressions is True or none of them is, in which ICASE return OTHERWISEVALUE. Note that expressions and values come in pairs except for OTHERWISEVALUE, which has no matching expression.

If SomeDate is 06/15/2004, ICASE(INLIST(MONTH(SomeDate), 1, 2, 3), "Quarter 1", INLIST(MONTH(SomeDate), 4, 5, 6), "Quarter 2", INLIST(MONTH(SomeDate), 7, 8, 9), "Quarter 3", "Quarter 4") returns "Quarter 2"

IIF( EXPRESSION, TRUE_RESULT, FALSE_RESULT )
If EXPRESSION is True, IIF (which stands for "Immediate IF") returns TRUE_RESULT. Otherwise, it returns FALSE_RESULT.

If SomeDate is 03/15/2004, IIF(INLIST(MONTH(SomeDate), 1, 2, 3), "Quarter 1", "Quarter 2") returns "Quarter 1"

INLIST( VALUE, ITEM1, ITEM2, ... )
Returns True if VALUE is contained in the specified list of items. Up to 24 items can be specified.

If SomeDate is 03/15/2004, INLIST(MONTH(SomeDate), 1, 2, 3) returns True.

LEFT( CHAR_VALUE, NUM_CHARS )
Returns the specified number of characters from the parameter, beginning at the first character on the left.

LEFT("Microsoft Windows", 5) returns "Micro"

LTRIM( CHAR_VALUE )
Removes any blanks from the beginning of the expression. "LTRIM" is an abbreviation for "left trim."

LTRIM(" This is a test ") returns "This is a test "

MINUTE( DATETIME_VALUE )
Returns the minute part of the specified date/time value.

If SomeDate is 03/30/2004 11:55:00 PM, MINUTE(SomeDate) returns 55

MOD( DIVIDEND, DIVISOR )
Divides one numeric expression by another and returns the remainder. This is known as the Modulus function.

If the current record in the data set for the report (obtained with the RECNO() function) is 11, MOD(RECNO(), 2) returns 1

MONTH( DATE_VALUE )
Returns the month part of the date parameter as a numeric value.

If SomeDate is 03/30/2004, MONTH(SomeDate) returns 3

PADL( VALUE, NUMBER_OF_CHARACTERS [, PAD_CHARACTER ] )
PADC( VALUE, NUMBER_OF_CHARACTERS [, PAD_CHARACTER ] )
PADR( VALUE, NUMBER_OF_CHARACTERS [, PAD_CHARACTER ] )
Returns a string padded with spaces or characters to a specified length on the left (PADL) or right sides (PADR) or both (PADC). The pad character is optional; if it isn't specified, space is used.

PADR("Hello", 10) returns "Hello " (five spaces at the end)
PADL("Hello", 10) returns " Hello" (five spaces at the start)
PADC("Hello", 10) returns " Hello " (two spaces at the start and three at the end)
PADR("Hello", 10, "-") returns "Hello-----"

QUARTER( DATE_VALUE [, YEARSTART ] )
Returns the quarter of the year the specified date is in. YEARSTART specifies an optional starting month if you want the quarter based on a fiscal year rather than a calendar year; use oApp.nYearStart if you want to use the Year starts in setting from the Options dialog.

If SomeDate is 06/30/2004, QUARTER(SomeDate) returns 2 while QUARTER(SomeDate, 7) (meaning the first month of the fiscal year is July) returns 4.

_ReportPageNo
This isn't a function, but a system variable that contains the current page number in a report.

_ReportPageTotal
This isn't a function, but a system variable that contains the total number of pages in a report.

RECCOUNT()
Returns the total number of records in the data set for the report. "RECCOUNT" is an abbreviation for "record count."

RECNO()
Returns the current record number in the data set for the report. "RECNO" is an abbreviation for "record number."

RunSQL( SQLStatement )
Executes the specified SQL statement, which should calculate a single value, and returns that value. See the Executing a SQL Statement During a Report Run topic for details.

SEC( DATETIME_VALUE )
Returns the seconds part of the specified date/time value. "SEC" is an abbreviation for "seconds."

If SomeDate is 03/31/2004 11:55:05 PM, SEC(SomeDate) returns 5.

SpelledOut( AMOUNT )
Spells out a numeric value.

SpelledOut(5.25) returns "Five dollars and 25 cents."

SQLookup( TableToLookIn, FieldToLookIn, ValueToSearchFor, ExpressionToReturn )
Retrieves the value for the ExpressionToReturn expression from the TableToLookIn table for the record where FieldToLookIn has ValueToSearchFor as its value. ExpressionToReturn can be a single field name to return the value in that field or an expression to return some type of calculation. The expression must be one the database engine can understand rather than a Stonefield Query expression. Normally, you need to put quotes around TableToLookIn, FieldToLookIn, and ExpressionToReturn. If ValueToSearchFor is the name of a field from the current data set, don't put quotes around it.

If you have a report that includes the Customer ID from the Orders table, SQLookup('Customers', 'CustomerID', CustomerID, 'CompanyName') returns the company name from the Customers table whose customer ID matches the current Customer ID.

STOD( VALUE, FORMAT )
Converts a character or date value in the specified format to a date value. Valid values for FORMAT are "YMD" to indicate that VALUE is in YYYYMMDD format, "DMY" for DDMMYYYY, or "MDY" for MMDDYYYY. If FORMAT isn't specified, "YMD" is assumed. If VALUE isn't a valid date, a blank date is returned. "STOD" is an abbreviation for"string to date conversion."

STOD(20100323) returns 03/23/2010.

STR( NUMERIC_VALUE, LENGTH, DECIMALS )
Converts a numeric value into a character value. LENGTH is the number of characters in the new string, including the decimal point. DECIMALS is the number of decimal places desired. If the number is too big for the allotted space, asterisks are returned. "STR" is an abbreviation for "string conversion."

STR(5.7, 4, 2) returns "5.70"

STRTRAN( CHAR_VALUE, FIND_VALUE, REPLACE_VALUE )
Searches the first character parameter for occurrences of the second and replaces each occurrence with the third. Specify an empty string or omit the third parameter to remove FIND_VALUE from CHAR_VALUE. "STRTRAN" is an abbreviation for "string transformation."

STRTRAN("Microsoft Windows", "Micro", "Mega") returns "Megasoft Windows"

SUBSTR( CHAR_VALUE, START_POSITION, NUM_CHARS)
A substring of the character value, starting from START_POSITION and being NUM_CHARS long, is returned. "SUBSTR" is an abbreviation for "sub-string."

SUBSTR("Microsoft Windows", 6, 4) returns "soft"

TRANSFORM( VALUE )
Converts the parameter into the equivalent character value. This is useful for concatenating character and non-character values together.

If the data set for the report has 20 records in it, "There are " + TRANSFORM(RECCOUNT()) + " records" returns "There are 20 records." TRANSFORM() is needed since RECCOUNT() returns a numeric value.

TRIM( CHAR_VALUE )
Removes any blanks from the end of the expression.

TRIM(" This is a test ") returns " This is a test"

TTOD( DATETIME_VALUE )
Converts a date/time value to a date value by dropping the time portion. "TTOD" is an abbreviation for "time to date conversion."

If SomeDate is 03/31/2004 11:55:00 PM, TTOD(SomeDate) returns 03/31/2004

UPPER( CHAR_VALUE )
Converts a character string to uppercase.

UPPER("Microsoft Windows") returns "MICROSOFT WINDOWS"

VAL( CHAR_VALUE )
Converts a character value to a numeric value. "VAL" is an abbreviation for "value conversion."

If SomeString is "10," VAL(SomeString) returns 10

ValuesCollection( VALUE1, VALUE2, ... )
Creates a ValuesCollection object with the specified values (up to 10) as members.

WEEK( DATE_VALUE [, FIRST_WEEK ] [, FIRST_DAY_OF_WEEK ]]) Returns the week number of the date parameter as a numeric value.

FIRST_WEEK specifies an optional starting week of the year: use 1 if the first week contains January 1st (the default if you omit this parameter), 2 if at least four days of the first week is in the current year, or 3 if the first week has seven days.

FIRST_DAY_OF_WEEK specifies an optional starting day of the week; use oApp.nFirstDay if you want to use the Week starts on setting from the Options dialog. Otherwise, use 1 for Sunday (the default if this isn't specified), 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday, 7 for Saturday, or 0 for the First day of week setting in the Regional Settings applet of the Windows Control Panel.

YEAR( DATE_VALUE )
Returns the year part of the date parameter as a numeric value.

If SomeDate is 03/30/2004, YEAR(SomeDate) returns 2004

Date Math

You can perform certain types of math on date values. Adding a numeric value to a date adds that many days to the date. Subtracting one date from another gives the number of days between those dates.

For example, if SomeDate1 is 03/30/2004 and SomeDate2 is 04/01/2004, SomeDate1 + 1 gives 03/31/2004 and SomeDate2 - SomeDate1 gives 2.

Some Useful Expressions

  • DATE() - DAY(DATE()) and GoMonthDay(DATE(), -1, 31) both give the last day of the previous month

  • DATE() - DAY(DATE()) + 1 and GoMonthDay(DATE(), 0, 1) both give the first day of the current month

  • GOMONTH(DATE() - DAY(DATE()) + 1, 1) - 1 and GoMonthDay(DATE(), 0, 31) both give the last day of the current month

  • GOMONTH(DATE(), -1) is this day last month

  • GOMONTH(DATE(), 12) is this day next year

  • A range of dates between DATE() - DAY(DATE()) + 1 (which gives the first day of the current month) and DATE() is the month-to-date.

  • A range of dates between GoMonthDay(DATE(), -1, 1) (which gives the first day of last month) and GOMONTH(DATE(), -1) is last month-to-date.

  • A range of dates between DATE(YEAR(DATE()), 1, 1) (which gives the first day of the current year) and DATE() is the year-to-date.

  • A range of dates between GOMONTH(DATE(YEAR(DATE()), 1, 1), -12) (which gives the first day of last year) and GOMONTH(DATE(), -12) is last year-to-date.

  • MOD(RECNO(), 2) alternates between 0 (for even-numbered records) and 1 (for odd-numbered records), so using MOD(RECNO(), 2) = 1 as the Print When expression for a colored rectangle can be used to print alternating bars on a report such as shown below.

© Stonefield Software Inc., 2024 • Updated: 01/18/22
Comment or report problem with topic