Article ID: 123361
Article Last Modified on 11/15/2004
APPLIES TO
- Microsoft Works 3.0 Standard Edition
- Microsoft Works 3.0a
- Microsoft Works 3.0b
- Microsoft Works 4.5 Standard Edition
- Microsoft Works 4.5a
- Microsoft Works 4.0 Standard Edition
- Microsoft Works 4.0a
This article was previously published under Q123361
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.
REFERENCES
For more information about how to perform this task in Works, see your Works printed documentation or online Help.
Additional query words: 3.00 3.00a 3.00b 4.00 4.00a 4.50 4.50a kbhowto together adding addition combine ampersand and sign non text non-text numbers w_works
Keywords: kberrmsg kbprb KB123361
