How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT null/0 (1 Viewer)

[Minddumps]

[SOLVED] How? Concatenate fields if Null/0 OR concatenate "these" fields instead if..

How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT null/0

Ok, I'm a bit stuck on figuring this out. In fact I don't even know how to word it to look for the solution since nothing I've come across quite relates completely.

I'm trying to work on a mail merge and one of my query columns says:

NameFull: [AllPOCs**].[Title] & " " & [POC First] & " " & [MI] & " " & [POC Last]

However, for my Mail Merge expression, I'm wanting something like this:

MergeName: If [AllPOCs**].[Title] is not empty or null, then = [AllPOCs**].[Title] & " " & [POC Last], but if [AllPOCs**].[Title] IS EMPTY then = [POC First]

Anyone come across this type of issue before and can help me with the code?

 

[Pat Hartman]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

You can do this with a series of complicated IIf()'s or you can take advantage of the different concatenation properties of the + sign. The standard VBA concatenation character is the &. This operator ignores nulls so a & null will result in a. But the + respects nulls and so a + null results in null.

NameFull: ([AllPOCs**].[Title] + " ") & [POC First] & " " & ([MI] + " ") & [POC Last]

Notice the parentheses. You need to force the expression to be evaluated the way you need to get rid of the extraneous spaces.

 

[speakers_86]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Is the asterisk a special character?

 

[Mihail]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Is this what you are looking for ?
IIF([Title] & "" <> "" ; WhatToReturnForNotNullOrEmpty ; WhatToReturnForNullOrEmpt)

If not, where is the mistake ?

 

[Minddumps]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

NameFull: ([AllPOCs**].[Title] + " ") & [POC First] & " " & ([MI] + " ") & [POC Last]

Thanks for that tip! I hadn't even noticed the extra spaces that the first bit left me with!

I'm still left with the issue however, that if a person has a Title I need it to reveal [Title, LastName] and if the person doesn't have a title inputted, I need the mail merge to show just [FirstName].

Do you by chance know how to do that??

 

[Minddumps]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Is the asterisk a special character?

Haha, no, that's because I was distinguishing this particular table from an ocean of others I had created. It was just so I could find it easier, by looking for the asterick.

 

[Minddumps]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Is this what you are looking for ?
IIF([Title] & "" <> "" ; WhatToReturnForNotNullOrEmpty ; WhatToReturnForNullOrEmpt)

If not, where is the mistake ?

Hmm, interesting attempt... but I'm told the expression entered contains invalid syntax. It doesn't seem to be accepting the semi-colons, but I'm not not educated enough in this subject to know for sure. Thanks for the try though!

 

[MSAccessRookie]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Hmm, interesting attempt... but I'm told the expression entered contains invalid syntax. It doesn't seem to be accepting the semi-colons, but I'm not not educated enough in this subject to know for sure. Thanks for the try though!

In the version of Access that I have, a Comma is the Proper separator for IIf() Statements. so try substituting Commas for the Semi-Colons and see what happens.

-- Rookie

 

[Brianwarnock]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

I think the ; , issue is a country issue not an access version.

Brian

 

[Minddumps]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

In the version of Access that I have, a Comma is the Proper separator for IIf() Statements. so try substituting Commas for the Semi-Colons and see what happens.

Wow!! I'm jumping for joy! My final attempt, changing ; to , worked!! It's always the simple things that kill me. I was trying to make it much too difficult in my previous attempts. Thank you!!!

For anyone else trying to figure out the issue, here's the working code:

MailMerge: IIf([AllPOCs**].[Title] & ""<>"",[AllPOCs**].[Title] & " " & [POC Last],[POC first])

 

[MSAccessRookie]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Wow!! I'm jumping for joy! My final attempt, changing ; to , worked!! It's always the simple things that kill me. I was trying to make it much too difficult in my previous attempts. Thank you!!!

For anyone else trying to figure out the issue, here's the working code:

MailMerge: IIf([AllPOCs**].[Title] & ""<>"",[AllPOCs**].[Title] & " " & [POC Last],[POC first])

Always glad to know that I can help out

 

[Mihail]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

It doesn't seem to be accepting the semi-colons, but I'm not not educated enough in this subject to know for sure.

The formula is intended to work as definition for a field in a query, not in VBA.
In VBA you should replace semi-colons with commas.

 

[Brianwarnock]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

I don't know the situation in many countries but in the US and UK. You use commas in SQL and the QBE also. I think some of the Scandinavia countries use the semi-colon, maybe you do in Eastern Europe.

Brian

 

[Mihail]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

I can only say what my Access accept. I Think that is not about the Country because I work in the same Access environment when I write formulas in VBA or when I write in query design window.
Exactly the same formula ask me for commas in VBA and for semicolons in query design window.
It is annoying because all the time I forget about semicolons.

 

[Brianwarnock]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

The syntax in VBA is probably the same the world over, hence we have to enter dates in US format, the Design Grid or QBE is a GUI ( graphical user interface) and accepts local style syntax.

Brian

 

[Pat Hartman]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

Do you by chance know how to do that??

Yes!!!! and my post #2 showed you how to do it by using the + instead of the & as a concatenation character.

Title + " " results in Null when the Title is null. Whereas Title & " " results in a single space which makes your name look mis-aligned.

 

[Mihail]

Re: How? Concatenate fields if Null/0 OR concatenate "these" fields instead if NOT nu

The syntax in VBA is probably the same the world over, hence we have to enter dates in US format, the Design Grid or QBE is a GUI ( graphical user interface) and accepts local style syntax.

Brian

Indeed. Just I've changed my local settings in the Control Panel and I see the changed in one of my queries.

Thank you very much Brian but, please, for the future, speak at least 2 years earlier