Hi, All
I have a customer contact info database, "Customer Name" & "Spause Name",
address, city..., when i am printing a report i like to combine "Customer
Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
make it show "David & Betty Howe" on my report?
thanks Carol.

If you are storing the customer's full name and the spouse's full name in
one field each, then there isn't any very reliable way to do it. You could
look for the space between first and last name, but that isn't always
reliable, because there can be more than one space within a name ('Billy
Bob', 'Mary Kate', 'van der Vorst' etc.).

You can do it easily enough if you store the first and last names in
separate fields, e.g. [CustomerFirstName] & " and " & [SpouseFirstName] & "
" & [CustomerLastName]. However, this assumes that the customer's last name
and the spouse's last name are the same. Do you really think that is a safe
assumption to make?

--
Brendan Reynolds
Access MVP

"Carol Shu" <(E-Mail Removed)> wrote in message
news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
> Hi, All
> I have a customer contact info database, "Customer Name" & "Spause Name",
> address, city..., when i am printing a report i like to combine "Customer
> Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
> make it show "David & Betty Howe" on my report?
> thanks Carol.

hi Carol,

Carol Shu wrote:
> I have a customer contact info database, "Customer Name" & "Spause Name",
> address, city..., when i am printing a report i like to combine "Customer
> Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
> make it show "David & Betty Howe" on my report?
Not really, because you have to extract the single strings, which is a
kind of sophisticated task.

Normally these kind of data should be stored normalized in your tables
(in separated fields):

Table Customer:
---------------
CustomerFirstName
CustomerSurname
SpouseFirstName
SpouseSurname

So you can create a query like this and use it as record source for your
report:

SELECT
IIf(CustomerSurname = SpouseSurname,
CustomerFirstName & " & " & SpouseFirstName & " " &
CustomerSurname,
CustomerFirstName & ", " & CustomerSurname &
SpouseFirstName & ", " & SpouseSurname)
FROM Customer

mfG
--> stefan <--

Carol,
It's really your table design taht is causing this problem to begin with. It strongly
suggest that you fix that first, than to struggle with this problem again and again in the
future.
Right now, you can't even sort your Customers by Last Name/First Name.

You should create a CustFirstName and CustLastName and a SpouseFirstName field in your
table, and break out those values into their respective discreet fieldss.. (avoid spaces
in field names)

Then this simple concatenation would do the job...
= CustFirstName & " & " & SpouseFirstName & " " & CustLastName
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Carol Shu" <(E-Mail Removed)> wrote in message
news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
> Hi, All
> I have a customer contact info database, "Customer Name" & "Spause Name",
> address, city..., when i am printing a report i like to combine "Customer
> Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
> make it show "David & Betty Howe" on my report?
> thanks Carol.

sorry, i just post the same question.
thank you all for replying, that's my book says have to create with last &
first name...and this that, I know i should done it when i create the
datebase, now it's to hard to change it there are about 2000 records. i have
one more question, how to combine the address, ie. city & state & zipcode in
one line, thank you.

"Al Campagna" wrote:

> Carol,
> It's really your table design taht is causing this problem to begin with. It strongly
> suggest that you fix that first, than to struggle with this problem again and again in the
> future.
> Right now, you can't even sort your Customers by Last Name/First Name.
>
> You should create a CustFirstName and CustLastName and a SpouseFirstName field in your
> table, and break out those values into their respective discreet fieldss.. (avoid spaces
> in field names)
>
> Then this simple concatenation would do the job...
> = CustFirstName & " & " & SpouseFirstName & " " & CustLastName
> --
> hth
> Al Campagna
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
> "Carol Shu" <(E-Mail Removed)> wrote in message
> news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
> > Hi, All
> > I have a customer contact info database, "Customer Name" & "Spause Name",
> > address, city..., when i am printing a report i like to combine "Customer
> > Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
> > make it show "David & Betty Howe" on my report?
> > thanks Carol.
>
>
>

=[City] & ", " & [State] & " " & [ZIP]


--
Rick B



"Carol Shu" <(E-Mail Removed)> wrote in message
news:2EEA7C77-C9D6-4EC8-875F-(E-Mail Removed)...
> sorry, i just post the same question.
> thank you all for replying, that's my book says have to create with last &
> first name...and this that, I know i should done it when i create the
> datebase, now it's to hard to change it there are about 2000 records. i
> have
> one more question, how to combine the address, ie. city & state & zipcode
> in
> one line, thank you.
>
> "Al Campagna" wrote:
>
>> Carol,
>> It's really your table design taht is causing this problem to begin
>> with. It strongly
>> suggest that you fix that first, than to struggle with this problem again
>> and again in the
>> future.
>> Right now, you can't even sort your Customers by Last Name/First Name.
>>
>> You should create a CustFirstName and CustLastName and a
>> SpouseFirstName field in your
>> table, and break out those values into their respective discreet
>> fieldss.. (avoid spaces
>> in field names)
>>
>> Then this simple concatenation would do the job...
>> = CustFirstName & " & " & SpouseFirstName & " " & CustLastName
>> --
>> hth
>> Al Campagna
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>> "Carol Shu" <(E-Mail Removed)> wrote in message
>> news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
>> > Hi, All
>> > I have a customer contact info database, "Customer Name" & "Spause
>> > Name",
>> > address, city..., when i am printing a report i like to combine
>> > "Customer
>> > Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way
>> > to
>> > make it show "David & Betty Howe" on my report?
>> > thanks Carol.
>>
>>
>>

thank you very much Rick.

"Rick B" wrote:

> =[City] & ", " & [State] & " " & [ZIP]
>
>
> --
> Rick B
>
>
>
> "Carol Shu" <(E-Mail Removed)> wrote in message
> news:2EEA7C77-C9D6-4EC8-875F-(E-Mail Removed)...
> > sorry, i just post the same question.
> > thank you all for replying, that's my book says have to create with last &
> > first name...and this that, I know i should done it when i create the
> > datebase, now it's to hard to change it there are about 2000 records. i
> > have
> > one more question, how to combine the address, ie. city & state & zipcode
> > in
> > one line, thank you.
> >
> > "Al Campagna" wrote:
> >
> >> Carol,
> >> It's really your table design taht is causing this problem to begin
> >> with. It strongly
> >> suggest that you fix that first, than to struggle with this problem again
> >> and again in the
> >> future.
> >> Right now, you can't even sort your Customers by Last Name/First Name.
> >>
> >> You should create a CustFirstName and CustLastName and a
> >> SpouseFirstName field in your
> >> table, and break out those values into their respective discreet
> >> fieldss.. (avoid spaces
> >> in field names)
> >>
> >> Then this simple concatenation would do the job...
> >> = CustFirstName & " & " & SpouseFirstName & " " & CustLastName
> >> --
> >> hth
> >> Al Campagna
> >> Candia Computer Consulting - Candia NH
> >> http://home.comcast.net/~cccsolutions
> >>
> >> "Carol Shu" <(E-Mail Removed)> wrote in message
> >> news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
> >> > Hi, All
> >> > I have a customer contact info database, "Customer Name" & "Spause
> >> > Name",
> >> > address, city..., when i am printing a report i like to combine
> >> > "Customer
> >> > Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way
> >> > to
> >> > make it show "David & Betty Howe" on my report?
> >> > thanks Carol.
> >>
> >>
> >>
>
>
>

Carol,
Not sure what your "book" says to do, but I think you can see that "discreet" fields
are the right way to go.
To do as you wanted originally...
** This will only work with data that has a firstname and a lastname in both fields,
separated by a space**
Any initials, or commas will cause the concatenation to display incorrectly...
(I'll use CustName SpouseName for my example)

=Left([CustName],InStr([CustName]," ")) & "& " & Left([SpouseName],InStr([SpouseName],"
")) & Mid([CustName],InStr([CustName]," "))

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Carol Shu" <(E-Mail Removed)> wrote in message
news:2EEA7C77-C9D6-4EC8-875F-(E-Mail Removed)...
> sorry, i just post the same question.
> thank you all for replying, that's my book says have to create with last &
> first name...and this that, I know i should done it when i create the
> datebase, now it's to hard to change it there are about 2000 records. i have
> one more question, how to combine the address, ie. city & state & zipcode in
> one line, thank you.
>
> "Al Campagna" wrote:
>
>> Carol,
>> It's really your table design taht is causing this problem to begin with. It
>> strongly
>> suggest that you fix that first, than to struggle with this problem again and again in
>> the
>> future.
>> Right now, you can't even sort your Customers by Last Name/First Name.
>>
>> You should create a CustFirstName and CustLastName and a SpouseFirstName field in
>> your
>> table, and break out those values into their respective discreet fieldss.. (avoid
>> spaces
>> in field names)
>>
>> Then this simple concatenation would do the job...
>> = CustFirstName & " & " & SpouseFirstName & " " & CustLastName
>> --
>> hth
>> Al Campagna
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>> "Carol Shu" <(E-Mail Removed)> wrote in message
>> news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
>> > Hi, All
>> > I have a customer contact info database, "Customer Name" & "Spause Name",
>> > address, city..., when i am printing a report i like to combine "Customer
>> > Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
>> > make it show "David & Betty Howe" on my report?
>> > thanks Carol.
>>
>>
>>

Thank you Al Campagna for your help. I used your example, and found there is
another problem, if customer has no spouse, so there will be error showing,
sometimes mexicans customers even has 2 last names, so i'm going to do the
wright way, i'm going to add a field for first, last names and spousefirst
names, lst names, i really appreciate your help.

"Al Campagna" wrote:

> Carol,
> Not sure what your "book" says to do, but I think you can see that "discreet" fields
> are the right way to go.
> To do as you wanted originally...
> ** This will only work with data that has a firstname and a lastname in both fields,
> separated by a space**
> Any initials, or commas will cause the concatenation to display incorrectly...
> (I'll use CustName SpouseName for my example)
>
> =Left([CustName],InStr([CustName]," ")) & "& " & Left([SpouseName],InStr([SpouseName],"
> ")) & Mid([CustName],InStr([CustName]," "))
>
> --
> hth
> Al Campagna
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
> "Carol Shu" <(E-Mail Removed)> wrote in message
> news:2EEA7C77-C9D6-4EC8-875F-(E-Mail Removed)...
> > sorry, i just post the same question.
> > thank you all for replying, that's my book says have to create with last &
> > first name...and this that, I know i should done it when i create the
> > datebase, now it's to hard to change it there are about 2000 records. i have
> > one more question, how to combine the address, ie. city & state & zipcode in
> > one line, thank you.
> >
> > "Al Campagna" wrote:
> >
> >> Carol,
> >> It's really your table design taht is causing this problem to begin with. It
> >> strongly
> >> suggest that you fix that first, than to struggle with this problem again and again in
> >> the
> >> future.
> >> Right now, you can't even sort your Customers by Last Name/First Name.
> >>
> >> You should create a CustFirstName and CustLastName and a SpouseFirstName field in
> >> your
> >> table, and break out those values into their respective discreet fieldss.. (avoid
> >> spaces
> >> in field names)
> >>
> >> Then this simple concatenation would do the job...
> >> = CustFirstName & " & " & SpouseFirstName & " " & CustLastName
> >> --
> >> hth
> >> Al Campagna
> >> Candia Computer Consulting - Candia NH
> >> http://home.comcast.net/~cccsolutions
> >>
> >> "Carol Shu" <(E-Mail Removed)> wrote in message
> >> news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
> >> > Hi, All
> >> > I have a customer contact info database, "Customer Name" & "Spause Name",
> >> > address, city..., when i am printing a report i like to combine "Customer
> >> > Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
> >> > make it show "David & Betty Howe" on my report?
> >> > thanks Carol.
> >>
> >>
> >>
>
>
>

Carol,
I hear what your saying, but this is all a consequence of a table design problem.
>> ** This will only work with data that has a firstname and a lastname in both fields,
>> separated by a space**
You're going to go nuts trying to program around all the data that does not conform to
the above rule.

You can break out the current data into discreet fields using Update queries. It's not
simple, but it's probably easier than struggling with what you have now, and worth the
investment. (hand editing 2000 records) I can't go into ALL the details, but here's a
snapshot on how to do it using String functions (Left, Mid, and InStr... so bone up on
those)

How to break "Bob Smith" into "Bob and Smith"
Add two new fields to your table... CustomerFirstName and CustomerLastName
1. Create an update query against the table that updates the new FirstName field with
Left(CustomerName, Instr(CustomerName, " ") - 1)
that puts "Bob" in the new CustomerFirstName field. Goodness...
2. Create an update query (or combine this with the previous query) that updates the
CustomerLastName field with...
Mid(CustomerName, Instr(CustomerName, " ") + 1)
that puts "Smith" in the new CustomerLastName field.
3. Repeat this same process for SpouseName to a new SpouseFirstName and
SpouseLastName field (if SpouseLastName is really needed... maybe just a firstname would
do... your call)

Expected result...
CustomerName CustomerFirst CustomerLastName
Bob Smith Bob Smith
SpouseName SpouseFirstName SpouseLastName
Mary Smith Mary Smith

This won't wotk on all your records, but I'll bet it will do most of them. You would
then hand edit those names that don't conform, and finally delete the old Name fields
altogether. Then you are all set to go... labels, sorting, finding names, everything will
be a breeze!
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"Carol Shu" <(E-Mail Removed)> wrote in message
news:CC2E48E9-180E-4BF0-844C-(E-Mail Removed)...
> Thank you Al Campagna for your help. I used your example, and found there is
> another problem, if customer has no spouse, so there will be error showing,
> sometimes mexicans customers even has 2 last names, so i'm going to do the
> wright way, i'm going to add a field for first, last names and spousefirst
> names, lst names, i really appreciate your help.
>
> "Al Campagna" wrote:
>
>> Carol,
>> Not sure what your "book" says to do, but I think you can see that "discreet" fields
>> are the right way to go.
>> To do as you wanted originally...
>> ** This will only work with data that has a firstname and a lastname in both fields,
>> separated by a space**
>> Any initials, or commas will cause the concatenation to display incorrectly...
>> (I'll use CustName SpouseName for my example)
>>
>> =Left([CustName],InStr([CustName]," ")) & "& " & Left([SpouseName],InStr([SpouseName],"
>> ")) & Mid([CustName],InStr([CustName]," "))
>>
>> --
>> hth
>> Al Campagna
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>> "Carol Shu" <(E-Mail Removed)> wrote in message
>> news:2EEA7C77-C9D6-4EC8-875F-(E-Mail Removed)...
>> > sorry, i just post the same question.
>> > thank you all for replying, that's my book says have to create with last &
>> > first name...and this that, I know i should done it when i create the
>> > datebase, now it's to hard to change it there are about 2000 records. i have
>> > one more question, how to combine the address, ie. city & state & zipcode in
>> > one line, thank you.
>> >
>> > "Al Campagna" wrote:
>> >
>> >> Carol,
>> >> It's really your table design taht is causing this problem to begin with. It
>> >> strongly
>> >> suggest that you fix that first, than to struggle with this problem again and again
>> >> in
>> >> the
>> >> future.
>> >> Right now, you can't even sort your Customers by Last Name/First Name.
>> >>
>> >> You should create a CustFirstName and CustLastName and a SpouseFirstName field in
>> >> your
>> >> table, and break out those values into their respective discreet fieldss.. (avoid
>> >> spaces
>> >> in field names)
>> >>
>> >> Then this simple concatenation would do the job...
>> >> = CustFirstName & " & " & SpouseFirstName & " " & CustLastName
>> >> --
>> >> hth
>> >> Al Campagna
>> >> Candia Computer Consulting - Candia NH
>> >> http://home.comcast.net/~cccsolutions
>> >>
>> >> "Carol Shu" <(E-Mail Removed)> wrote in message
>> >> news0E71ECE-9CAC-4610-B8D2-(E-Mail Removed)...
>> >> > Hi, All
>> >> > I have a customer contact info database, "Customer Name" & "Spause Name",
>> >> > address, city..., when i am printing a report i like to combine "Customer
>> >> > Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
>> >> > make it show "David & Betty Howe" on my report?
>> >> > thanks Carol.
>> >>
>> >>
>> >>
>>
>>
>>