How to concatenate multiple values in text box

  • Thread starter Thread starter HeislerKurt
  • Start date Start date
H

HeislerKurt

I'm trying to code a control source for a text box to concatenate and
handle various possible values. There are three fields I need to
handle:

For example, if the values for the three fields are ...

Person_Relation = "Parent"
Person_Relation_Other = <NULL>
Person_Relation_Type = "Biological"

.... my text box should show:

Parent :Biological;

(The ":" and ";" will be replaced by "(" and ")" but for now it's
easier to use something other than parentheses for testing.)

If instead the values for the three fields are:

Person_Relation = "Other"
Person_Relation_Other = "Neighbor"
Person_Relation_Type = <NULL>

.... my text box should show

Neighbor

This is what I have so far:

=IIf(IsNull([Person_Relation]),"",[Person_Relation] & (" :"+
[Person_Relation_Type] & ";"))

If the person is a Biological Parent, my current code correctly shows:

Parent :Biological;

But of the person is an Other: Neighbor, my current code shows:

Other;

So, if Person_Relation = "Other" I need to figure out how to:

1. Omit Person_Relation & Person_Relation_Type in the string
2. Show the value for Person_Relation_Other
3. Omit the trailing ";"

Any ideas? Been tweaking and tweaking to no avail. Thank you.
 
Shouldn't you be checking whether Person_Relation_Type is Null, not
Person_Relation?

Try:

=IIf(IsNull([Person_Relation_Type]),[Person_Relation_Other],[Person_Relation]
& (" :"+[Person_Relation_Type] & ";"))

Alternatively, you could base it on what's in Person_Relation using:

=IIf(Nz([Person_Relation], "Other") = "Other", [Person_Relation_Other],
[Person_Relation] & (" :"+[Person_Relation_Type] & ";"))
 
Alternatively, you could base it on what's in Person_Relation using:
=IIf(Nz([Person_Relation], "Other") = "Other", [Person_Relation_Other],
[Person_Relation] & (" :"+[Person_Relation_Type] & ";"))
Click to expand...

I went with this option and it works great. Thank you.
 
Alternatively, you could base it on what's in Person_Relation using:
Click to expand...
=IIf(Nz([Person_Relation], "Other") = "Other", [Person_Relation_Other],
[Person_Relation] & (" :"+[Person_Relation_Type] & ";"))
Click to expand...

I went with this option and it works great. Thank you.
Click to expand...

Actually ... just noticed that if Person_Relation has a value (e.g.,
Parent), but Person_Relation_Type is null, the last semicolon appears
as in:

Parent;

In this case, it should be hidden. I can't seem to figure out the fix.
 
Back
Top