Concatenate 2 fields in textbox

Question

I'm trying to concatenate 2 fields from my database in 1 textbox like I did many times before setting the record source like this =[parent] & " - " & [parentCity].

It doesn't work.Instead, it writes #type! in the textbox.

If I choose in the record source parent or parentCity separately it works.

Also, no control are named parent or parentCity and both fields are varchar

Then I thought it was because of null so I did something ugly like this

=IIf(IsNull([parent]),"",IIf(IsNull([parentCity]),[parent],[parent] & [parentCity]))

I get the same error and i tried

=IIf(IsNull([parent]),"",IIf(IsNull([parentCity]),[parent],"LOL"))

And it works so the problem is definitively [parent] & " - " & [parentCity]

Does anyone know what's wrong here.

Thank you

Answer 1

[Parent] is throwing off your Textbox as it is trying to access the Parent form/report (Confirmed through experimentation.) This could be potentially useful but keeps you from being able to display the value of the field Parent when you use an = sign, because Access assumes you are referring to the form's parent.

For reference, here is an extensive list of words that may cause problems in Access. Note that parent does appear on this list.

A work around would be to include in your form/report whatever a field that displays the value of parent (which you could hide) let's pretend you call it HIddenParent.

If you do that work around, you will be able to achieve the result you desire by using this as the source: =[HiddenParent] & " - " & [parentcity]

Answer 2

Create a text box named "txtParent" bound to the "parent" field. Create another named "txtParentCity" bound to the "parentCity" field.

If you don't want to see those 2 text boxes in form view, set their Visible property to No.

Whether or not they are visible, you can reference their values in the control source property of your target text box.

=[txtParent] & " - " & [txtParentCity]

Null for either or both of txtParent and txtParentCity will not trigger an error because concatenating Null with any string value (such as " - ") will still return that string value.