concatenating in access

[Guest]

I have been trying to set up a library database in access and have been
having some difficulty in concatenating authors names, when there is more
than one author (there may be up to four). My problem is I can't get rid of
the commas in the columns that do not contain more than one author. I have
searched the discussions here and have not come across this problem.

ie: Author1
Smith, John
ABC Press,
where ABC Press is the LastName

then when I combine Author1 & Author2 and Author2 is blank I have the same
problem.
I would like to be able to combine all four authors into one Authors column
with one query.
I'm sure there is a simple explanation, but it is eluding me.

 

[John Vinson]

I have been trying to set up a library database in access and have been
having some difficulty in concatenating authors names, when there is more
than one author (there may be up to four). My problem is I can't get rid of
the commas in the columns that do not contain more than one author. I have
searched the discussions here and have not come across this problem.

ie: Author1
Smith, John
ABC Press,
where ABC Press is the LastName

then when I combine Author1 & Author2 and Author2 is blank I have the same
problem.
I would like to be able to combine all four authors into one Authors column
with one query.
I'm sure there is a simple explanation, but it is eluding me.

I presume you're concatenating using an expression such as

[LastName] & ", " & [FirstName]

correct?

If so, you can use a sneaky trick: both the & operator and the +
operator concatenate strings, but + propagates NULLS - any string +
NULL is NULL - while & treats NULL as a zero length string.

Try

LastName & (", " + [FirstName])

If FirstName is NULL, the parenthetical expression will be NULL and
you won't see the comma.

John W. Vinson[MVP]

 

[Guest]

John
Thank you for your prompt response. I have tried the + operator before but
it only seems to affect about half of the query, the other half seems to
retain the commas.
I have tried to reinput the data thinking that I may have made an error but
the problem persists. Any ideas??

 

[John Vinson]

John
Thank you for your prompt response. I have tried the + operator before but
it only seems to affect about half of the query, the other half seems to
retain the commas.
I have tried to reinput the data thinking that I may have made an error but
the problem persists. Any ideas??

Please post the SQL of the query. I have no way of knowing what
you'redoing, so I obviously cannot tell what you're doing wrong.

Also indicate the current and the desired appearance of the output.

John W. Vinson[MVP]

 

[Guest]

Hello Again
Here is the expression I used and the result:

Author1: [FirstLastName] & (", "+[FirstFirstName])

Conroy, Mary
Rodale Press
Better Homes & Gardens
Roen, Fran
Benberry, Cuesta Ray
Thomas, Donna
Thomas, Donna
Rolfe, Margaret
Pellman, Rachael
ABC Press,
Carter, Roxanne
Suit, Mary
Fons, Marianne
Rodale Press,
Mosey, Caron
Porter, Liz
Jenkins, Susan
Lawson, Susy
Horton, Roberta
Anchor,
Hickey, Mary

As you can see it works the first two times as in Rodale Press and
Better Homes & Gardens but then reverts back as in the second Rodale Press
and continues to work sporadically throughout the 800+ authors

The end result that I would like is to be able to join all the authors
either by first or last name as in the following two examples:

Conroy, Mary, Hickey, Mary & Green, Cora
or
Mary Conroy, Mary Hickey & Cora Green

Thanks

 

[Douglas J Steele]

That implies to me that FirstFirstName isn't Null, but contains a space or
zero-length string for the 2nd Rodale Press (and for the other ones where
it's not working)

You could try the following (which will be much less efficient):

Author1: [FirstLastName] & IIf(Len([FirstFirstName] & "") > 0, ",
"+[FirstFirstName], "")

or, if there are still problems,

Author1: [FirstLastName] & IIf(Len(Trim([FirstFirstName] & "")) > 0, ",
"+[FirstFirstName], "")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Again
Here is the expression I used and the result:

Author1: [FirstLastName] & (", "+[FirstFirstName])

Conroy, Mary
Rodale Press
Better Homes & Gardens
Roen, Fran
Benberry, Cuesta Ray
Thomas, Donna
Thomas, Donna
Rolfe, Margaret
Pellman, Rachael
ABC Press,
Carter, Roxanne
Suit, Mary
Fons, Marianne
Rodale Press,
Mosey, Caron
Porter, Liz
Jenkins, Susan
Lawson, Susy
Horton, Roberta
Anchor,
Hickey, Mary

As you can see it works the first two times as in Rodale Press and
Better Homes & Gardens but then reverts back as in the second Rodale Press
and continues to work sporadically throughout the 800+ authors

The end result that I would like is to be able to join all the authors
either by first or last name as in the following two examples:

Conroy, Mary, Hickey, Mary & Green, Cora
or
Mary Conroy, Mary Hickey & Cora Green

Thanks

Please post the SQL of the query. I have no way of knowing what
you'redoing, so I obviously cannot tell what you're doing wrong.

Also indicate the current and the desired appearance of the output.

John W. Vinson[MVP]

 

[Guest]

Hi Doug,
Checked to make sure that the FirstFirstName was null and reran the query.
Same result.
The expressions that you sent got rid of the commas on the FirstLastName
only and retained them on the FirstFirstName and FirstLastName but the first
names did not appear in the query. Does that make sense?
ie:
Rolfe,
Pellman,
ABC Press
Carter,
Fons,
Rodale Press

That implies to me that FirstFirstName isn't Null, but contains a space or
zero-length string for the 2nd Rodale Press (and for the other ones where
it's not working)

You could try the following (which will be much less efficient):

Author1: [FirstLastName] & IIf(Len([FirstFirstName] & "") > 0, ",
"+[FirstFirstName], "")

or, if there are still problems,

Author1: [FirstLastName] & IIf(Len(Trim([FirstFirstName] & "")) > 0, ",
"+[FirstFirstName], "")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Again
Here is the expression I used and the result:

Author1: [FirstLastName] & (", "+[FirstFirstName])

Conroy, Mary
Rodale Press
Better Homes & Gardens
Roen, Fran
Benberry, Cuesta Ray
Thomas, Donna
Thomas, Donna
Rolfe, Margaret
Pellman, Rachael
ABC Press,
Carter, Roxanne
Suit, Mary
Fons, Marianne
Rodale Press,
Mosey, Caron
Porter, Liz
Jenkins, Susan
Lawson, Susy
Horton, Roberta
Anchor,
Hickey, Mary

As you can see it works the first two times as in Rodale Press and
Better Homes & Gardens but then reverts back as in the second Rodale Press
and continues to work sporadically throughout the 800+ authors

The end result that I would like is to be able to join all the authors
either by first or last name as in the following two examples:

Conroy, Mary, Hickey, Mary & Green, Cora
or
Mary Conroy, Mary Hickey & Cora Green

Thanks

Thank you for your prompt response. I have tried the + operator before but
it only seems to affect about half of the query, the other half seems to
retain the commas.
I have tried to reinput the data thinking that I may have made an error but
the problem persists. Any ideas??

Please post the SQL of the query. I have no way of knowing what
you'redoing, so I obviously cannot tell what you're doing wrong.

Also indicate the current and the desired appearance of the output.

John W. Vinson[MVP]

 

[Douglas J Steele]

No, it doesn't make sense!

I just noticed a minor thing in what I'd posted (should have used &, not +,
for concatenation in the IIf statement:

Author1: [FirstLastName] & IIf(Len([FirstFirstName] & "") > 0, ", " &
[FirstFirstName], "")

) but that shouldn't make a difference in this case, and certainly not what
you're seeing.

Just to make sure you didn't get confused by wordwrap, the 3 parameters of
the IIf statement should be:

IIf(
Len([FirstFirstName] & "") > 0
,
", " & [FirstFirstName]
,
""
)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,
Checked to make sure that the FirstFirstName was null and reran the query.
Same result.
The expressions that you sent got rid of the commas on the FirstLastName
only and retained them on the FirstFirstName and FirstLastName but the first
names did not appear in the query. Does that make sense?
ie:
Rolfe,
Pellman,
ABC Press
Carter,
Fons,
Rodale Press

That implies to me that FirstFirstName isn't Null, but contains a space or
zero-length string for the 2nd Rodale Press (and for the other ones where
it's not working)

You could try the following (which will be much less efficient):

Author1: [FirstLastName] & IIf(Len([FirstFirstName] & "") > 0, ",
"+[FirstFirstName], "")

or, if there are still problems,

Author1: [FirstLastName] & IIf(Len(Trim([FirstFirstName] & "")) > 0, ",
"+[FirstFirstName], "")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Again
Here is the expression I used and the result:

Author1: [FirstLastName] & (", "+[FirstFirstName])

Conroy, Mary
Rodale Press
Better Homes & Gardens
Roen, Fran
Benberry, Cuesta Ray
Thomas, Donna
Thomas, Donna
Rolfe, Margaret
Pellman, Rachael
ABC Press,
Carter, Roxanne
Suit, Mary
Fons, Marianne
Rodale Press,
Mosey, Caron
Porter, Liz
Jenkins, Susan
Lawson, Susy
Horton, Roberta
Anchor,
Hickey, Mary

As you can see it works the first two times as in Rodale Press and
Better Homes & Gardens but then reverts back as in the second Rodale Press
and continues to work sporadically throughout the 800+ authors

The end result that I would like is to be able to join all the authors
either by first or last name as in the following two examples:

Conroy, Mary, Hickey, Mary & Green, Cora
or
Mary Conroy, Mary Hickey & Cora Green

Thanks

Thank you for your prompt response. I have tried the + operator

before
but

it only seems to affect about half of the query, the other half

seems
to

retain the commas.
I have tried to reinput the data thinking that I may have made an

error
but

the problem persists. Any ideas??

Please post the SQL of the query. I have no way of knowing what
you'redoing, so I obviously cannot tell what you're doing wrong.

Also indicate the current and the desired appearance of the output.

John W. Vinson[MVP]

 

[Guest]

Doug:
I have solved my problem!!
Your statement made me think something being in the cell even if I deleted
and re-entered it. I copied the columns that were affected from access and
pasted them into excel and ran the TRIM function and pasted them back into
access and tada!! my original concatenating formula worked with the + instead
of &. I still don't know what caused the problem, the entries were made over
several years, in several versions of access???
Thanks to all for your timely advice and assistance.

 

[John Vinson]

Hello Again
Here is the expression I used and the result:

Author1: [FirstLastName] & (", "+[FirstFirstName])

As you can see it works the first two times as in Rodale Press and
Better Homes & Gardens but then reverts back as in the second Rodale Press
and continues to work sporadically throughout the 800+ authors

What's actually stored in the FirstFirstName field for these records?
Do you perhaps have the field's Allow Zero Length property set to True
(if so, the field might contain an empty string "" rather than a
NULL).

One quick test would be to run a query with a criterion of

IS NOT NULL AND NOT LIKE "?*"

on the FirstFirstName field; this will show you all the non-NULL
values which don't contain at least one character.

The end result that I would like is to be able to join all the authors
either by first or last name as in the following two examples:

Conroy, Mary, Hickey, Mary & Green, Cora
or
Mary Conroy, Mary Hickey & Cora Green

You'll need some VBA code to do that. See

http://www.mvps.org/access/modules/mdl0004.htm


John W. Vinson[MVP]