Scratch:
FWIW the field is named City, not Field1. I'm using that as an example.
Understood. In general, it's iffy changing the question to something generic. You rarely know what will break when doing that. In this case I see no difference but for a benefit of nothing obvious, it's not worth considering that approach. As I say though, no obvious harm in this case.
Now for the strange part. I did some testing in one of my databases (2003 in this case) and saw the
#Error come up a lot. To understand what I'm about to say you must understand the difference between a
Field and a
Control.
Controls are often, mistakenly, referred to as
Fields. The data they show can be from a
Field but the item on the form or report is actually a
Control. Examples of
Controls are TextBoxes; ComboBoxes; Labels; CheckBoxes; etc. In my testing I used two TextBox
controls to refer to another by its
Field name and, separately, by its
Control name (
Control Source = "=[PrimaryID] & ' - X'" and
Control Source = "=[txtPrimaryID] & ' - X'"). At this point I'd refer you back to
JForbes' post #7 where he describes the difference.
I first tried with a TextBox which had an
Input Mask set and the
.Value of [PrimaryID] was
Null. In this case the value when using the
Control reference was
#Error. I used the same reference on another TextBox which had no
Input Mask set and the value was fine (" - X"). I tried the same on a record where the
.Value of [PrimaryID] was "ON00203". In this case both were fine and showed the expected result (ON00203 - X). So far so good - but not much good if it doesn't handle
Null values.
Then I tried with the
Field reference instead. This time the
Control with the
Input Mask would show
#Error in some circumstances but not others. The other
Control always showed the desired result, whether on a record with
Null or not.
I suggest you take the
Control you're working with and check other properties of the
Control (Especially
Input Mask and
Format) to ensure any data shown would be valid. Then ensure your reference is to the
Field [City] and not the
Control. To do this you will need to make sure the
Control has a different name from the
Field.
Let us know how you get on.