Concatenating with and/or without a comma

[Owl]

My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

 

[Daryl S]

Owl -

The difference is in the parentheses:

Contact: IIf(Trim([Surname])="",Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName])))

 

[John W. Vinson]

My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

There's a sneaky shortcut: both the & and the + operators concatenate strings,
but + "propagates nulls" - returns NULL if either argument is NULL; the &
operator treats NULL as an empty string. Try:

Contact: ([Surname] + ", ") & [FirstName]

If Surname exists you'll get the expression in parentheses as "Jones, "; if it
doesn't, then the parentheses expression will be NULL.

 

[Owl]

I see that I had an extra parenthesis at the end, but I put in what you said
in my query and it gives me COMMA SPACE FIRSTNAME when there is only a
FirstName, and not just FirstName without the comma and the space. It looks
so right to me, and I can't see where it is wrong.

Owl -

The difference is in the parentheses:

Contact: IIf(Trim([Surname])="",Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName])))

--
Daryl S

My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

 

[Owl]

That gives me SURNAME COMMA if there is only a surname. I don't want it to
have the comma if there is only a surname.

If there is only FIRSTNAME, it isn't showing at all. I need it to show and
with no comma and no space.

It is only if there is both, that it works correctly.

Thanks for the input, though both of you.

Please can you help me further.

My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

There's a sneaky shortcut: both the & and the + operators concatenate strings,
but + "propagates nulls" - returns NULL if either argument is NULL; the &
operator treats NULL as an empty string. Try:

Contact: ([Surname] + ", ") & [FirstName]

If Surname exists you'll get the expression in parentheses as "Jones, "; if it
doesn't, then the parentheses expression will be NULL.

 

[Owl]

Thanks, both of you. I've got it. It is:


Contact: IIf(Trim([Surname]) & ""="","",Trim([Surname]) & ", ") &
Trim([FirstName])

That gives me SURNAME COMMA if there is only a surname. I don't want it to
have the comma if there is only a surname.

If there is only FIRSTNAME, it isn't showing at all. I need it to show and
with no comma and no space.

It is only if there is both, that it works correctly.

Thanks for the input, though both of you.

Please can you help me further.

My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

There's a sneaky shortcut: both the & and the + operators concatenate strings,
but + "propagates nulls" - returns NULL if either argument is NULL; the &
operator treats NULL as an empty string. Try:

Contact: ([Surname] + ", ") & [FirstName]

If Surname exists you'll get the expression in parentheses as "Jones, "; if it
doesn't, then the parentheses expression will be NULL.

 

[John W. Vinson]

That gives me SURNAME COMMA if there is only a surname. I don't want it to
have the comma if there is only a surname.

If there is only FIRSTNAME, it isn't showing at all. I need it to show and
with no comma and no space.

It is only if there is both, that it works correctly.

Sorry.... I was answering the question that you actually asked, rather than
the one you intended to ask: to quote,

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

I take it that you have three possibilities: only a surname (in which case you
just want that); only a firstname (in which case you just want it); or both,
in which case you want the comma? If so, you'll need some complexity:

[Surname] & (Iif(IsNull([Surname]) OR IsNull([LastName], "", ", ") &
[Lastname]

 

[Owl]

OOPS! It's still not right. I am getting the following with the previous
attempt:

Mike
Wilson, Mary
Jones,

The first two are fine. However, I don't want a comma following the
surname. Can you help me get rid of it, please, based on the criterion in my
previous response, I would imagine.

Thank you for any help.

Thanks, both of you. I've got it. It is:


Contact: IIf(Trim([Surname]) & ""="","",Trim([Surname]) & ", ") &
Trim([FirstName])

That gives me SURNAME COMMA if there is only a surname. I don't want it to
have the comma if there is only a surname.

If there is only FIRSTNAME, it isn't showing at all. I need it to show and
with no comma and no space.

It is only if there is both, that it works correctly.

Thanks for the input, though both of you.

Please can you help me further.

My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

There's a sneaky shortcut: both the & and the + operators concatenate strings,
but + "propagates nulls" - returns NULL if either argument is NULL; the &
operator treats NULL as an empty string. Try:

Contact: ([Surname] + ", ") & [FirstName]

If Surname exists you'll get the expression in parentheses as "Jones, "; if it
doesn't, then the parentheses expression will be NULL.

 

[Owl]

Just to clarify: I want the comma after the Wilson example, but not after the
Jones example.

OOPS! It's still not right. I am getting the following with the previous
attempt:

Mike
Wilson, Mary
Jones,

The first two are fine. However, I don't want a comma following the
surname. Can you help me get rid of it, please, based on the criterion in my
previous response, I would imagine.

Thank you for any help.

Thanks, both of you. I've got it. It is:


Contact: IIf(Trim([Surname]) & ""="","",Trim([Surname]) & ", ") &
Trim([FirstName])

That gives me SURNAME COMMA if there is only a surname. I don't want it to
have the comma if there is only a surname.

If there is only FIRSTNAME, it isn't showing at all. I need it to show and
with no comma and no space.

It is only if there is both, that it works correctly.

Thanks for the input, though both of you.

Please can you help me further.




:


My parameter in Criteria in a query is the following:

Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName]))))

I am intending it to give me:

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

Can somebody please correct the above parameter so that it will give me what
I am wanting?

Thank you for any help.

There's a sneaky shortcut: both the & and the + operators concatenate strings,
but + "propagates nulls" - returns NULL if either argument is NULL; the &
operator treats NULL as an empty string. Try:

Contact: ([Surname] + ", ") & [FirstName]

If Surname exists you'll get the expression in parentheses as "Jones, "; if it
doesn't, then the parentheses expression will be NULL.

 

[Owl]

Sorry for having been confusing and thank you very much for your time.

I tried what you said (with FirstName instead of LastName because Surname
and LastName are the same thing), but it didn't accept one of the middle
parentheses and when I took it out, it said there were too many arguments.

However, in the meantime I found an entry in Queries by Ofer Cohen which
worked. It is very similar to what you said. He said (to somebody else's
question):


Contact: ([Surname]) & IIf(Trim([Surname] & "")="",[FirstName],(",
"+[FirstName]))

Thank you for your help nevertheless. You guys and gals are wonderful and
get so many of us out of a fix. I appreciate you all tremendously.

That gives me SURNAME COMMA if there is only a surname. I don't want it to
have the comma if there is only a surname.

If there is only FIRSTNAME, it isn't showing at all. I need it to show and
with no comma and no space.

It is only if there is both, that it works correctly.

Sorry.... I was answering the question that you actually asked, rather than
the one you intended to ask: to quote,

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

I take it that you have three possibilities: only a surname (in which case you
just want that); only a firstname (in which case you just want it); or both,
in which case you want the comma? If so, you'll need some complexity:

[Surname] & (Iif(IsNull([Surname]) OR IsNull([LastName], "", ", ") &
[Lastname]

 

[John W. Vinson]

OOPS! It's still not right. I am getting the following with the previous
attempt:

Mike
Wilson, Mary
Jones,

The first two are fine. However, I don't want a comma following the
surname. Can you help me get rid of it, please, based on the criterion in my
previous response, I would imagine.

Reposting, in case you didn't get it:


Sorry.... I was answering the question that you actually asked, rather than
the one you intended to ask: to quote,

Surname, FirstName
but if there is no Surname, then:
FirstName (without the space and the comma)

I take it that you have three possibilities: only a surname (in which case you
just want that); only a firstname (in which case you just want it); or both,
in which case you want the comma? If so, you'll need some complexity:

[Surname] & (Iif(IsNull([Surname]) OR IsNull([LastName], "", ", ") &
[Lastname]