Concatenate text fields produces #Error message

[dching]

I'm concatenating two text fields using the following string:

="Total " & [Section] & " Tests:"

However, when I run this report, I get a #Error for the object.

I also have another concatenated field using the following string:

="Total " & [Subsection] & " Tests:"

When I run the report using the above string, I get a positive return.

Why is this happening? Please note that the Section field is located in "Table A" and Subsection field is located in "Table B".

Both fields (Section and Subsection) is flagged as a text field in each of their respective tables.

Also, when just using the Section field as part of the detail section in the report, it returns the correct information.

Thanks in advance for your help.

 

[Pat Hartman]

Make sure that the control's Name property is not the same as the name of a bound column. The Name property is the first property on the Other tab.

 

[dching]

Pat,

Thanks for the reply. I've looked at the report and also created a quick and dirty just using the two table fields. There is no duplication of the bound and unbound fields.

To provide you with additional information.

Table 1 Fields.
Index
Section

Table 2 Fields
Index
Index Link to Table 1
Subsection

So far, in the quick and dirty report, I have selected both of the tables for the report as well as creating unbound fields for both of these control objects.

[Section]
[Subsection]
unbound 1 [=[Section]]
unbound 2 [=[Subsection]]

When I run this report, the bound fields populate with the correct answer. The unbound 2 field populates with the correct answer. The unbound 1 field returns with a #Error answer.

Reviewing both of the tables, the Section and Subsection field properties are exactly the same (type=text).

I am definitely puzzled.

Thanks. Derek

 

[dching]

Found the Answer!

Pat,

Found the answer. "Section" is a VB command. Therefore, once you use it in a concatenated string, it will produce an error.

The workaround is to create a query and create an expression using the [Section] field, i.e.

SectionX:[Section]

Whereby SectionX is the new name.

Once you create this new field, you can then use it in a concatenated string.

Persistence does have it's rewards.

Thanks again for your help.

Derek

 

[Rich]

Why not just change the field name in table?

 

[dching]

I normally would, however, the entire db is built with multiple reports, queries and forms, sub-forms and sub-reports.

Not that I'm lazy (nah, I am lazy) but to go through it at this time would be quite challenging; unless there is a way to change all of the names in all instances.

If you can provide me with a quick and easy way to find all of the instances and change it retroactively, I'd be much appreciative.

Thanks.
Derek

 

[Rich]

Rick Fishers Find and Replace gets recommended as does the tool from FMS

 

[dching]

Thanks for the lead. I'll check it out.

Derek

 

[Pat Hartman]

If you're using A2K or newer, Access has limited built in ability to "change" references. The newer your version of Access, the better this feature works.
Make a backup copy.
Go to Tools/Options and make sure that all the "name auto correct" features are checked. In theory now if you change the column name in a table, the references to it from forms/reports/queries will be updated. No code will be updated though.

BTW if you're using A2K you MUST have service pack 2 or 3 installed.