Combining two fields Help

[Juan4412]

I have a query that is combining two fields:

Full Name: [FirstName] & " " & [LastName]

Sometimes last name may be null, how can I set the query to only display LastName if it is not null?

[JoeM]

Is the issue the extra space at the end if there is no last name?
You could use and IF statement along with ISNULL, or something as simple as this:

Full Name: Trim([FirstName] & " " & [LastName])

[Juan4412]

That fixed problem 1. Thank you.

My next problem is I am running this code:

Address: Replace([Address1] & [Address2] & [Address3], "|", ",")

The issue I have here is 2 fold...1) There is always a space between address1 and address2 I would like it to just read address1, address2, address3. Then if there is no address3 not place a comma after address2

[JoeM]

Can you post a few examples of what can be in Address1, Address2, and Address3 and what your final result should look like?

[Juan4412]

Desired outcome:
1100 N Main Street, Office 12, Suite 110 (if all 3 fields have data)
1100 N Main Street, Office 12 (if only 2 fields have data)
1100 N Main Street (if only one field has data)

And that is also the data that will be in the fields. Does that help clarify?

[JoeM]

How about:

Address: [Address1] & IIF(LEN([Address2])>0,", " & [Address2],"") & IIF(LEN([Address3])>0,", " & [Address3],"")

[Juan4412]

That is working with the exception of needing a comma between Address1 & Address2 if address2 exists. At least on my end.
So...
1100 N Main Street, Office 12
Or Read
1100 N Main Street

[JoeM]

That is working with the exception of needing a comma between Address1 & Address2 if address2 exists. At least on my end.

Its there. Maybe you didn't copy my formula completely.
Take a look again at my previous post, and notice in each IIF statement a comma and space that appears before it returns the appropriate Address field.

[Juan4412]

Oh you are correct, I apologize for my oversight. and thank you greatly for the assistance.

[JoeM]

Glad to help!

[Juan4412]

I marked this as solved as it was working then, but I must have changed something to not have it working now

This is the syntax
Total: Replace([FirstOffer] & IIf(Len([Offer2])>0,", " & [Offer2],""),"|",",")

And it tells me undefined function 'Replace' in expression???

[JoeM]

Not sure. Your formula works for me, exactly as you have written it.