Ask a new question

Access reports- combining first & last names

in Access reports, I print =Trim([FirstName] & " & " & [SpouseName] & " " & [LastName]), like Dick & Sally Jones.  Unmarried names come out & Sally Jones.  How do I trim the & if there is no spouse?

IIf(IsNull([SpouseName]);[FirstName] & " " & [Lastname];[FirstName] & " & " & [SpouseName] & " " & [LastName])

 

Peter (http://access.xps350.com/)
Groeten,

Peter

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You've got more problems than that. It's the 21st century... my name is John Vinson, my wife's name is Karen Strickler! She'll be justly piqued if you use your logic.

That said, Peter's IIF will work fine (for the question as stated); you can also use a sneaky feature. The & and + operators both concatenate strings, but & treats a NULL field as a zero length string, while + treats it as a NULL, returning NULL as a result of the concatenation. So you could use

[FirstName] & (" & " + [SpouseName]) & " " & [LastName]

The expression in parentheses will evaluate to NULL if the SpouseName is null.

 

John W. Vinson/MVP
John W. Vinson/MVP

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 9, 2021 Views 1,576 Applies to: