About functions

The ADDINT$ function adds an interval to a date.

Syntax: ADDINT$(interval,number,date)

Interval is the period of time by which you want to increment a date. Use the following to denote the interval: d = day, w = week, m = month, q = quarter, y = year.

Number specifies number of intervals by which you want to increment a date.

Date is the date or date field to which the intervals are added.

Example: To create a list of payable invoices whose payments are overdue by two days for the given discount date, use the following expression:

ADDINT$(d,2,acpinv.dscdte)

The expression adds two days to the payable invoice discount date and compares the new value to the system date (today’s date) provided by the DATE$ function.

The AGED function determines if a date falls between two values.

Syntax: AGED (date,number1,number2)

Date is a date or date field.

Number1 and number2 specify the number of days that the date must fall between to return a logical True.

A “1” is returned by the AGED function when the date falls within the range and “0” returned when it doesn’t.

Example: To create a calculation that checks the age of receivable invoices based on their due dates, use the following expression:

AGED(acrinv.duedte, 1, 30)

The expression determines if the due date is less than or equal to the system date plus 30 days.

The CASE function conditionally returns a result based on the value of an item.

Syntax: CASE(item,value-n,result-n,default)

Item is the type of data being checked.

Value-n is the value for which the CASE function is searching.

Result-n is the result the CASE function returns when the CASE function finds Value-n.

Default is the result if the CASE function does not find Value-n.

Example: Suppose you want to print or display a description of the payable invoice status. To do this, create the following expression:

CASE(acpinv.status,1,Open,2,Review,3, Dispute,4,Paid,Void)

The expression includes a value and a result for invoice statuses 1–4. If the data that is returned by the acpinv.status field does not match any of the values, then the expression returns the default found at the end of the expression, Void.

The DATE$ function returns the system date.

Syntax: DATE$

No additional information is necessary.

Example: To print or display the system date when the report was created, insert the following function where you want the date to appear in your report:

DATE$

If the system date is 09/05/10, the field returns the value 09/05/2010.

The DAY function returns the number of the day of the month.

Syntax: DAY(date)

Date is the date field.

Example: To print or display the day of the month for a receivable invoice date, use the following expression:

DAY(acrinv.invdte)

If the date is 09/05/2010, the field returns the value 10.

The DAY$ function returns of day of the month as a character string.

Syntax: DAY$(number)

Number is the number of the day, where 1 through 7 represents Sunday through Saturday.

Example: To print or display the day of the week for a fixed date in a schedule, use the following expression:

DAY$(schlin.fxddte)

If the fixed date 09/05/2010 falls on a Wednesday, the field returns Wednesday.

The LEFT$ function returns a specified number of characters beginning at the left-most character.

Syntax: LEFT$(string,number)

String is the field that contains the characters you want to select.

Number specifies the number of characters you want to select.

Example: Suppose you are creating an employee list and you want to include the employee’s first initial and last name. To print or display the first letter of the employee’s first name, use the following expression:

LEFT$(employ.fstnme,1)

If the employee’s first name is Ron, Sage 100 Contractor returns R.

The MID$ function returns a number of characters from a character string, starting at a position you specify.

Syntax: MID$(string,number1,number2)

String is the field that contains the characters you want to select.

Number1 specifies the position of the first character.

Number2 specifies the number of characters you want to select.

Example: Suppose you are creating a lumber list, and you only want to include dimensional lumber that is described in six characters, such as 2x4x20. You do not want to print or display other lumber sizes such as 4x8.

MID$(string,1,6)

If the string contains six characters, this function returns six-character strings, such as 2x4x20.

The MONTH function returns the number of the month from a date field.

Syntax: MONTH(date)

Date is the date field.

Example: To print or display the month for a fixed date in a schedule, use the following expression:

MONTH(schlin.fxddte)

If the fixed date is 09/05/2010, the field returns the value 9.

The MONTH$ function returns the month as a character string.

Syntax: MONTH$(number)

Number is the number of the month, where 1 through 12 represents January through December.

Example: To print or display the month for a fixed date in a schedule, use the following expression:

MONTH$(schlin.fxddte)

If the fixed date is 09/05/2010, the field returns September.

The RIGHT$ function returns a specified number of characters beginning at the right-most character.

Syntax: RIGHT$(string,number)

String is the field that contains the characters you want to select.

Number specifies the number of characters you want to select.

Example: Suppose you want to create a list of equipment that includes the model year, which you include as the last information in the equipment description:

RIGHT$(eqpmnt.eqpnme,4)

If the equipment description is Cat 3054T Diesel Engine 2000, Sage 100 Contractor returns 2000.

The SPELL$ function spells out the currency value. Report Writer returns ***VOID*** if the number is zero or a negative.

Syntax: SPELL$(number)

Number is the number or field you want to spell out.

Example: To print or display the payable invoice balance, use the following expression:

SPELL$(acpinv.invbal)

If the payable invoice balance is $535.00, the field returns FIVE HUNDRED THIRTY FIVE DOLLARS.

The TRIM$ function removes the trailing spaces in a character expression.

Syntax: TRIM$(string)

String is the field from which you want to remove the trailing spaces.

Example: The employee first name field can contain up to 20 characters. If an employee’s first name is Gerald, which contains six characters, the employee first name field would return the name plus 14 empty spaces.

TRIM$(employ.fstnme)

Using TRIM$ removes the trailing 16 spaces.