Concatenation Problem

G

Guest

Hi, All,

I'm new, confused and apologize for the interruption, but I've got a puzzle
and hope someone can help.

I have a table that is growing and now has over 2200 entries. Many of these
entries relate to one another. The purpose of this particular table is both
to list and cross-reference the records to one another.

Names are listed in 4 fields: First Name, Middle Name, Last Name and
Organizational Designation (e.g. & Sons, Co., Inc.). I am trying to write a
query that will concatenate those 4 fields into one. The purpose of the query
is that it will be the "Look Up" for the cross-reference fields in this table.

A majority of entries only have a "Last Name". Some contain information in
all 4 fields, some in 3 fields, some in 2, but ALL have the "Last Name".

As will surprise no one, the simple expression

[PEN MAKER EXPERIMENTAL]![PnMkrFNm] +" "+ [PEN MAKER
EXPERIMENTAL]![PnMkrMNm] +" "+ [PEN MAKER EXPERIMENTAL]![PnMkrLNm] +" "+ [PEN
MAKER EXPERIMENTAL]![PnMkrOrg] )

didn't work at all. It returned ONLY those entries where all 4 fields
contained data.

Then I got a little more sophisticated. Using the expression builder, I came
up with the following:

Expr1: (IIf((([PEN MAKER EXPERIMENTAL]!PnMkrFNm) Or ([PEN MAKER
EXPERIMENTAL]!PnMkrMNm) Or ([PEN MAKER EXPERIMENTAL]!PnMkrOrg)) Is Not
Null,([PEN MAKER EXPERIMENTAL]!PnMkrFNm+" "+[PEN MAKER
EXPERIMENTAL]!PnMkrMNm+" "+[PEN MAKER EXPERIMENTAL]!PnMkrLNm+" "+[PEN MAKER
EXPERIMENTAL]!PnMkrOrg),([PEN MAKER EXPERIMENTAL]!PnMkrLNm)))

That got better results, but not perfect results. In some cases where only
one, two or all three of the fields linked by "Or" are blank, the expression
returns nothing at all. It seems to do it randomly (e.g. the 20th & 22nd
records are blank, as are the 45th, 47th & 48th, the 88th, the 103rd & 105th
& 119th) and without real logic.

I tried inverting the argument as follows:

Expr1: (IIf((([PEN MAKER EXPERIMENTAL]!PnMkrFNm) Or ([PEN MAKER
EXPERIMENTAL]!PnMkrMNm) Or ([PEN MAKER EXPERIMENTAL]!PnMkrOrg)) Is Null,([PEN
MAKER EXPERIMENTAL]!PnMkrLNm),([PEN MAKER EXPERIMENTAL]!PnMkrFNm+" "+[PEN
MAKER EXPERIMENTAL]!PnMkrMNm+" "+[PEN MAKER EXPERIMENTAL]!PnMkrLNm+" "+[PEN
MAKER EXPERIMENTAL]!PnMkrOrg)))

but got an identical result.

So, my question, good folks, is this: what do I do to get the thing to
concatenate under all circumstances?

If only the "PnMkrLNm" field contains data, I need that data returned in
this query. And if one or more of the "PnMkrFNm", "PnMkrMNm" or "PnMkrOrg"
fields contain data, I need them to concatenate with "PnMkrLNm", under all
circumstances, to return the combined data.

I'm not fluent in any programming language and consider it something of a
miracle that I was able to construct the 2 "IIf" expressions above and get
them to work at all.

I'm running Office 2000.

Thank you in advance for any help you can give.

Take care,

Rob Astyk
 
M

Michel Walsh

Hi,



With Jet? Use & rather than + if you want to concatenate nullable
strings while forgetting NULL values:

", " + LastName


will return NULL if LastName is NULL


FirstName & ( ", " + LastName )


will return FirstName alone, if LastName is Null; will return FirstName, a
coma and LastName if LastName is not Null


FirstName & ", " & LastName


will return FirstName and a coma, if LastName is null. So, the second
formulation seems more appropriate, with a right use of + and & .



With MS SQL Server?

FirstName + COALESCE( ', ' + LastName, ' ' ) + COALESCE( '('
+ title + ')' , ' ' )

as example, can be used. You can surely do the same in Jet, replaces
COALESCE with NZ



Hoping it may help,
Vanderghast, Access MVP

robastyk said:
Hi, All,

I'm new, confused and apologize for the interruption, but I've got a
puzzle
and hope someone can help.

I have a table that is growing and now has over 2200 entries. Many of
these
entries relate to one another. The purpose of this particular table is
both
to list and cross-reference the records to one another.

Names are listed in 4 fields: First Name, Middle Name, Last Name and
Organizational Designation (e.g. & Sons, Co., Inc.). I am trying to write
a
query that will concatenate those 4 fields into one. The purpose of the
query
is that it will be the "Look Up" for the cross-reference fields in this
table.

A majority of entries only have a "Last Name". Some contain information in
all 4 fields, some in 3 fields, some in 2, but ALL have the "Last Name".

As will surprise no one, the simple expression

[PEN MAKER EXPERIMENTAL]![PnMkrFNm] +" "+ [PEN MAKER
EXPERIMENTAL]![PnMkrMNm] +" "+ [PEN MAKER EXPERIMENTAL]![PnMkrLNm] +" "+
[PEN
MAKER EXPERIMENTAL]![PnMkrOrg] )

didn't work at all. It returned ONLY those entries where all 4 fields
contained data.

Then I got a little more sophisticated. Using the expression builder, I
came
up with the following:

Expr1: (IIf((([PEN MAKER EXPERIMENTAL]!PnMkrFNm) Or ([PEN MAKER
EXPERIMENTAL]!PnMkrMNm) Or ([PEN MAKER EXPERIMENTAL]!PnMkrOrg)) Is Not
Null,([PEN MAKER EXPERIMENTAL]!PnMkrFNm+" "+[PEN MAKER
EXPERIMENTAL]!PnMkrMNm+" "+[PEN MAKER EXPERIMENTAL]!PnMkrLNm+" "+[PEN
MAKER
EXPERIMENTAL]!PnMkrOrg),([PEN MAKER EXPERIMENTAL]!PnMkrLNm)))

That got better results, but not perfect results. In some cases where
only
one, two or all three of the fields linked by "Or" are blank, the
expression
returns nothing at all. It seems to do it randomly (e.g. the 20th & 22nd
records are blank, as are the 45th, 47th & 48th, the 88th, the 103rd &
105th
& 119th) and without real logic.

I tried inverting the argument as follows:

Expr1: (IIf((([PEN MAKER EXPERIMENTAL]!PnMkrFNm) Or ([PEN MAKER
EXPERIMENTAL]!PnMkrMNm) Or ([PEN MAKER EXPERIMENTAL]!PnMkrOrg)) Is
Null,([PEN
MAKER EXPERIMENTAL]!PnMkrLNm),([PEN MAKER EXPERIMENTAL]!PnMkrFNm+" "+[PEN
MAKER EXPERIMENTAL]!PnMkrMNm+" "+[PEN MAKER EXPERIMENTAL]!PnMkrLNm+"
"+[PEN
MAKER EXPERIMENTAL]!PnMkrOrg)))

but got an identical result.

So, my question, good folks, is this: what do I do to get the thing to
concatenate under all circumstances?

If only the "PnMkrLNm" field contains data, I need that data returned in
this query. And if one or more of the "PnMkrFNm", "PnMkrMNm" or "PnMkrOrg"
fields contain data, I need them to concatenate with "PnMkrLNm", under all
circumstances, to return the combined data.

I'm not fluent in any programming language and consider it something of a
miracle that I was able to construct the 2 "IIf" expressions above and get
them to work at all.

I'm running Office 2000.

Thank you in advance for any help you can give.

Take care,

Rob Astyk
Click to expand...
 
G

Guest

Hi, Michael,

Thank you! It works beautifully. I was obviously looking for a complicated
solution to a simple problem when a simple solution was best. Thanks! That's
exactly what I needed.

Take care,

Rob Astyk

Michel Walsh said:
Hi,



With Jet? Use & rather than + if you want to concatenate nullable
strings while forgetting NULL values:

", " + LastName


will return NULL if LastName is NULL


FirstName & ( ", " + LastName )


will return FirstName alone, if LastName is Null; will return FirstName, a
coma and LastName if LastName is not Null


FirstName & ", " & LastName


will return FirstName and a coma, if LastName is null. So, the second
formulation seems more appropriate, with a right use of + and & .



With MS SQL Server?

FirstName + COALESCE( ', ' + LastName, ' ' ) + COALESCE( '('
+ title + ')' , ' ' )

as example, can be used. You can surely do the same in Jet, replaces
COALESCE with NZ



Hoping it may help,
Vanderghast, Access MVP

robastyk said:
Hi, All,

I'm new, confused and apologize for the interruption, but I've got a
puzzle
and hope someone can help.

I have a table that is growing and now has over 2200 entries. Many of
these
entries relate to one another. The purpose of this particular table is
both
to list and cross-reference the records to one another.

Names are listed in 4 fields: First Name, Middle Name, Last Name and
Organizational Designation (e.g. & Sons, Co., Inc.). I am trying to write
a
query that will concatenate those 4 fields into one. The purpose of the
query
is that it will be the "Look Up" for the cross-reference fields in this
table.

A majority of entries only have a "Last Name". Some contain information in
all 4 fields, some in 3 fields, some in 2, but ALL have the "Last Name".

As will surprise no one, the simple expression

[PEN MAKER EXPERIMENTAL]![PnMkrFNm] +" "+ [PEN MAKER
EXPERIMENTAL]![PnMkrMNm] +" "+ [PEN MAKER EXPERIMENTAL]![PnMkrLNm] +" "+
[PEN
MAKER EXPERIMENTAL]![PnMkrOrg] )

didn't work at all. It returned ONLY those entries where all 4 fields
contained data.

Then I got a little more sophisticated. Using the expression builder, I
came
up with the following:

Expr1: (IIf((([PEN MAKER EXPERIMENTAL]!PnMkrFNm) Or ([PEN MAKER
EXPERIMENTAL]!PnMkrMNm) Or ([PEN MAKER EXPERIMENTAL]!PnMkrOrg)) Is Not
Null,([PEN MAKER EXPERIMENTAL]!PnMkrFNm+" "+[PEN MAKER
EXPERIMENTAL]!PnMkrMNm+" "+[PEN MAKER EXPERIMENTAL]!PnMkrLNm+" "+[PEN
MAKER
EXPERIMENTAL]!PnMkrOrg),([PEN MAKER EXPERIMENTAL]!PnMkrLNm)))

That got better results, but not perfect results. In some cases where
only
one, two or all three of the fields linked by "Or" are blank, the
expression
returns nothing at all. It seems to do it randomly (e.g. the 20th & 22nd
records are blank, as are the 45th, 47th & 48th, the 88th, the 103rd &
105th
& 119th) and without real logic.

I tried inverting the argument as follows:

Expr1: (IIf((([PEN MAKER EXPERIMENTAL]!PnMkrFNm) Or ([PEN MAKER
EXPERIMENTAL]!PnMkrMNm) Or ([PEN MAKER EXPERIMENTAL]!PnMkrOrg)) Is
Null,([PEN
MAKER EXPERIMENTAL]!PnMkrLNm),([PEN MAKER EXPERIMENTAL]!PnMkrFNm+" "+[PEN
MAKER EXPERIMENTAL]!PnMkrMNm+" "+[PEN MAKER EXPERIMENTAL]!PnMkrLNm+"
"+[PEN
MAKER EXPERIMENTAL]!PnMkrOrg)))

but got an identical result.

So, my question, good folks, is this: what do I do to get the thing to
concatenate under all circumstances?

If only the "PnMkrLNm" field contains data, I need that data returned in
this query. And if one or more of the "PnMkrFNm", "PnMkrMNm" or "PnMkrOrg"
fields contain data, I need them to concatenate with "PnMkrLNm", under all
circumstances, to return the combined data.

I'm not fluent in any programming language and consider it something of a
miracle that I was able to construct the 2 "IIf" expressions above and get
them to work at all.

I'm running Office 2000.

Thank you in advance for any help you can give.

Take care,

Rob Astyk
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Very difficult query (?) 9
Mail merge default file location 4
How do i get lines on a line graph? 2
mail merging filtered records..! 0
How do i get lines on a line graph? 2
Missing ; at end of stmt? 1
Count partial matched text string 4
XP on a really small disk..... 3

Top