Joining fields

Topic

New Reply

Should be easy but this is a real struggle for me tonight. If I want to join First Name + Spouse (if there is one) + Last Name, I used the following in my label report:

=Trim([FirstName] & ” and ” & [Spouse] & ” ” & [LastName])

The problem arises because some people don’t have a spouse, and no matter what I do, I still end up with John and Harrison instead of John Harrison.

Can you help?

Thanks

Reply | Quote

Replies

Try this:

=[FirstName] & ” and ” + [Spouse] & ” ” & [LastName]

Reply | Quote

Hi,
I would change the query feeding the report adding a calculated field

Name: IIf([Table1]![Spouse] Is Null,[Table1]![FirstName] & ” ” & [Table1]![LastName],[Table1]![FirstName] & ” and ” & [Table1]![Spouse] & ” ” & [Table1]![LastName])

and then just use the field Name

Reply | Quote

Mark’s expression: [FirstName] & ” and ” + [Spouse] & ” ” & [LastName] achieves exactly the same result as the longer

IIf([Table1]![Spouse] Is Null,[Table1]![FirstName] & ” ” & [Table1]![LastName],[Table1]![FirstName] & ” and ” & [Table1]![Spouse] & ” ” & [Table1]![LastName])

whether you put it in a calculated field in the query, or directly in the report (because of the way the + operator behaves.)

PS: Name is a reserved word in Access, so it is best to avoid using it as a field name. So if you want to use a calculated field in the query, find another name for it, such as Person or ContactName etc.

Reply | Quote

Doesn’t Marks expression leave 2 blanks between Firstname and Lastname?

I think this over comes that problem {note the round brackets enclosing ” and ” + [Spouse]}:
[FirstName] & (” and ” + [Spouse]) & ” ” & [LastName]

I have just tested both and they are both the same, so it doesn’t matter if the round brackets are included or not. So Mark’s solution is correct.

Reply | Quote

Doesn’t Marks expression leave 2 blanks between Firstname and Lastname?

I think this over comes that problem {note the round brackets enclosing ” and ” + [Spouse]}:
[FirstName] & (” and ” + [Spouse]) & ” ” & [LastName]

My solution will only produce a single space if only a first and last name.

The parentheses aren’t really needed, as the + is evaluated before the &. Sometimes, however, it is easier to read with them in.

Reply | Quote