#Error in a report field for no earthly reason

[Paul James]

Three of the fields in the recordset for a report are named City, State and
Zip.

I can display each of these fields in my report individually without a
problem. However, when I try to combine them with the expression:

=City&", "&State&" "&Zip

What makes this even more puzzling is that Print Preview displays "#Error"
in the calculated field. In Design View, Access converts the expression
above to

=[City] & ", " & [State] & " " & [Zip]

Access obviously a) recognizes the fields and b) has no problem with the
syntax. Moreover, there is only one table in the underlying query
recordset. And again, the report displays the individual City, State and
Zip fields in the report without an problem. So why would Access produce
the #Error when I try to combine them in an expression?

Thanks in advance,

Paul

 

[Paul James]

Three of the fields in the recordset for one of my reports are named City,
State and Zip.

I can display each of these fields in the report individually without a
problem. However, when I try to combine them with the expression:

=City&", "&State&" "&Zip

Print Preview displays "#Error" in the calculated field.

What makes this even more puzzling is that in Design View, Access converts
the expression above to

=[City] & ", " & [State] & " " & [Zip]

Access obviously a) recognizes the fields and b) has no problem with the
syntax. Moreover, there is only one table in the underlying query recordset.
And again, the report displays the individual City, State and

Zip fields in the report without an problem. So why would Access produce the
#Error when I try to combine them in an expression?

Thanks in advance,

Paul

 

[Duane Hookom]

Paul,
Make sure the name of the text box is NOT also the name of a field.

 

[Marshall Barton]

Three of the fields in the recordset for a report are named City, State and
Zip.

I can display each of these fields in my report individually without a
problem. However, when I try to combine them with the expression:

=City&", "&State&" "&Zip

What makes this even more puzzling is that Print Preview displays "#Error"
in the calculated field. In Design View, Access converts the expression
above to

=[City] & ", " & [State] & " " & [Zip]

Access obviously a) recognizes the fields and b) has no problem with the
syntax. Moreover, there is only one table in the underlying query
recordset. And again, the report displays the individual City, State and
Zip fields in the report without an problem. So why would Access produce
the #Error when I try to combine them in an expression?

Check to make sure the text box is not named the same as any
of the fields in the expression.

 

[Paul James]

Ah! That's it. You were right.

Thanks, guys.