Works: Spreadsheet or Database Returns ERR Using the & Operator (123361)

SYMPTOMS

The Works for Windows spreadsheet and database will display "ERR" in place of the desired data if you try to concatenate a nontext cell or field with another cell or field using the "&" (ampersand) operator. The nontext value could be a date (for example, 10/26/94), a month (for example, October), a time (for example, 12:34 pm), or just plain numbers (for example, 12345). Dates, including month names, and times are nontext because they are stored internally by Works as a serial number for calculation purposes.

RESOLUTION

To successfully concatenate nontext values, change the fields or cells to text by adding a quotation mark, or use the STRING function as described in the workarounds below. Try one of the following workarounds.

Method 1

Place a quotation mark (") before each nontext value in that field to force it to be text. Note that simply formatting a field or cell as text by choosing Number from the Format dialog and selecting Text will not change existing numbers to text.

Method 2

Create a new cell or field in the spreadsheet or database and use the STRING function to convert the values to text.

NOTE: For more information on the STRING function,

Example of Using the STRING Function to Concatenate a Date Field

The following formula can be entered as one line into the new field or cell:

="The date is "&STRING(MONTH(Date fieldname),0)&"/"&STRING(DAY(Date fieldname),0)&"/"&STRING(YEAR(Date fieldname),0)

Example of Using the STRING Function to Concatenate a Numeric Field

="The balance is "&STRING(AcctBalance,2)

For more information about concatenating text and numeric values, query on the following in the Microsoft Knowledge Base:

107073 Works: How to Append Text to Numbers in a Spreadsheet or Database

NOTE: Because the database report does not accept the STRING function, you must enter the formula in a new field in the database itself, then reference that new field in the report.

MORE INFORMATION

The "&" symbol is used to concatenate, or combine, two or more fields or cells, each of which must contain text. For example, if you want to have a database report combine the contents of the fields "Firstname" and "Lastname" with a space, enter the following formula in the report Record row:

=firstname&" "&lastname

This would result in the following:

Bill Smith
Joe Jones
Elisabeth Johnson

However, the "&" text concatenation operator cannot be used with nontext values without using one of the above workarounds.

NOTE: Works for Windows version 2.0 does NOT support using text in formulas or the text concatenation operator.