By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,297 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,297 IT Pros & Developers. It's quick & easy.

Special Labels

P: n/a
I am working on a fairly simple database for our bike club where we
keep records of the following:
First Name: Spouse Name: Address
I would like the labels to print the first name & spouse's name if
they are a member and not have the & sign if their spouse is not a
member.

Example: Jack & Jane Smith
222 Cane Street
Utica, MI 48316
or Jack Smith ( if spouse is not a member)
222 Cane Street
Utica, MI 48316

How do I do this?

Thank you

Bill
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:
I am working on a fairly simple database for our bike club where we
keep records of the following:
First Name: Spouse Name: Address
I would like the labels to print the first name & spouse's name if
they are a member and not have the & sign if their spouse is not a
member.

Example: Jack & Jane Smith
222 Cane Street
Utica, MI 48316

or Jack Smith ( if spouse is not a member)
222 Cane Street
Utica, MI 48316

How do I do this?

Thank you

Bill


[FirstName] & ("&"+ [Spouse Name])

If the SpouseName is null the "&" will not print.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #2

P: n/a
When I did like Fred showed me
=Trim([First Name] & " " & ("&"+[Spouse]) & " " & [Last Name]
I need to get a space between the first name and the spouse.'
It is reading as follows:
Jack &Jane Smith
222 Cane Street
Utica, MI 48316

Thanks again.

Bill

On Mon, 14 Feb 2005 17:34:39 GMT, fredg <fg******@example.invalid>
wrote:
On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:
I am working on a fairly simple database for our bike club where we
keep records of the following:
First Name: Spouse Name: Address
I would like the labels to print the first name & spouse's name if
they are a member and not have the & sign if their spouse is not a
member.

Example: Jack & Jane Smith
222 Cane Street
Utica, MI 48316

or Jack Smith ( if spouse is not a member)
222 Cane Street
Utica, MI 48316

How do I do this?

Thank you

Bill

I did like Fred told
[FirstName] & ("&"+ [Spouse Name])

If the SpouseName is null the "&" will not print.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


Nov 13 '05 #3

P: n/a
On Mon, 14 Feb 2005 23:08:10 GMT, Bill Smith wrote:
When I did like Fred showed me
=Trim([First Name] & " " & ("&"+[Spouse]) & " " & [Last Name]
I need to get a space between the first name and the spouse.'
It is reading as follows:
Jack &Jane Smith
222 Cane Street
Utica, MI 48316

Thanks again.

Bill

On Mon, 14 Feb 2005 17:34:39 GMT, fredg <fg******@example.invalid>
wrote:
On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:
I am working on a fairly simple database for our bike club where we
keep records of the following:
First Name: Spouse Name: Address
I would like the labels to print the first name & spouse's name if
they are a member and not have the & sign if their spouse is not a
member.

Example: Jack & Jane Smith
222 Cane Street
Utica, MI 48316

or Jack Smith ( if spouse is not a member)
222 Cane Street
Utica, MI 48316

How do I do this?

Thank you

Bill

I did like Fred told
[FirstName] & ("&"+ [Spouse Name])

If the SpouseName is null the "&" will not print.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Put the spaces around the & as below.

[FirstName] & (" & "+[SpouseName]) & " " & [LastName]

Jack & Jane Smith
Jack Smith

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #4

P: n/a
Great job Fred. It works fine. Now the only problem I have is I have
this very large database and under spouse I have a "space&name".This
is the only way I knew how to do it before but I knew it was not
right. Now I would like to go and clean up my database by find and
replace. I would like to find all the "space&name" and remove the
space& and just have the name. Is there a way I can do it?

Thanks again.

Bill
On Tue, 15 Feb 2005 02:24:12 GMT, fredg <fg******@example.invalid>
wrote:
On Mon, 14 Feb 2005 23:08:10 GMT, Bill Smith wrote:
When I did like Fred showed me
=Trim([First Name] & " " & ("&"+[Spouse]) & " " & [Last Name]
I need to get a space between the first name and the spouse.'
It is reading as follows:
Jack &Jane Smith
222 Cane Street
Utica, MI 48316

Thanks again.

Bill

On Mon, 14 Feb 2005 17:34:39 GMT, fredg <fg******@example.invalid>
wrote:
On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:

I am working on a fairly simple database for our bike club where we
keep records of the following:
First Name: Spouse Name: Address
I would like the labels to print the first name & spouse's name if
they are a member and not have the & sign if their spouse is not a
member.

Example: Jack & Jane Smith
222 Cane Street
Utica, MI 48316

or Jack Smith ( if spouse is not a member)
222 Cane Street
Utica, MI 48316

How do I do this?

Thank you

Bill
I did like Fred told
[FirstName] & ("&"+ [Spouse Name])

If the SpouseName is null the "&" will not print.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Put the spaces around the & as below.

[FirstName] & (" & "+[SpouseName]) & " " & [LastName]

Jack & Jane Smith
Jack Smith

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


Nov 13 '05 #5

P: n/a
bs******@comcast.net (Bill Smith) wrote in
news:42***************@news.mi.comcast.giganews.co m:
Great job Fred. It works fine. Now the only problem I have is
I have this very large database and under spouse I have a
"space&name".This is the only way I knew how to do it before
but I knew it was not right. Now I would like to go and clean
up my database by find and replace. I would like to find all
the "space&name" and remove the space& and just have the name.
Is there a way I can do it?

Thanks again.

Bill

create an update query. field one should be [spouse]. the
"replace with" for this column should be trim([spouse])

running that will remove all the leading spaces

Bob

On Tue, 15 Feb 2005 02:24:12 GMT, fredg
<fg******@example.invalid> wrote:
On Mon, 14 Feb 2005 23:08:10 GMT, Bill Smith wrote:
When I did like Fred showed me
=Trim([First Name] & " " & ("&"+[Spouse]) & " " & [Last
Name] I need to get a space between the first name and the
spouse.' It is reading as follows:
Jack &Jane Smith
222 Cane Street
Utica, MI 48316

Thanks again.

Bill

On Mon, 14 Feb 2005 17:34:39 GMT, fredg
<fg******@example.invalid> wrote:

On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:

> I am working on a fairly simple database for our bike club
> where we keep records of the following:
> First Name: Spouse Name: Address
> I would like the labels to print the first name & spouse's
> name if they are a member and not have the & sign if their
> spouse is not a member.
>
> Example: Jack & Jane Smith
> 222 Cane Street
> Utica, MI 48316
>
> or Jack Smith ( if spouse is not a member)
> 222 Cane Street
> Utica, MI 48316
>
> How do I do this?
>
> Thank you
>
> Bill
I did like Fred told
[FirstName] & ("&"+ [Spouse Name])

If the SpouseName is null the "&" will not print.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Put the spaces around the & as below.

[FirstName] & (" & "+[SpouseName]) & " " & [LastName]

Jack & Jane Smith
Jack Smith

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.



--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #6

P: n/a
Bob , will this remove the & in the field. I am really a very basic
user and would like some more direction. Thanks

Bill

On Tue, 15 Feb 2005 22:36:52 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:
bs******@comcast.net (Bill Smith) wrote in
news:42***************@news.mi.comcast.giganews.c om:
Great job Fred. It works fine. Now the only problem I have is
I have this very large database and under spouse I have a
"space&name".This is the only way I knew how to do it before
but I knew it was not right. Now I would like to go and clean
up my database by find and replace. I would like to find all
the "space&name" and remove the space& and just have the name.
Is there a way I can do it?

Thanks again.

Bill

create an update query. field one should be [spouse]. the
"replace with" for this column should be trim([spouse])

running that will remove all the leading spaces

Bob

On Tue, 15 Feb 2005 02:24:12 GMT, fredg
<fg******@example.invalid> wrote:
On Mon, 14 Feb 2005 23:08:10 GMT, Bill Smith wrote:

When I did like Fred showed me
=Trim([First Name] & " " & ("&"+[Spouse]) & " " & [Last
Name] I need to get a space between the first name and the
spouse.' It is reading as follows:
Jack &Jane Smith
222 Cane Street
Utica, MI 48316

Thanks again.

Bill

On Mon, 14 Feb 2005 17:34:39 GMT, fredg
<fg******@example.invalid> wrote:

>On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:
>
>> I am working on a fairly simple database for our bike club
>> where we keep records of the following:
>> First Name: Spouse Name: Address
>> I would like the labels to print the first name & spouse's
>> name if they are a member and not have the & sign if their
>> spouse is not a member.
>>
>> Example: Jack & Jane Smith
>> 222 Cane Street
>> Utica, MI 48316
>>
>> or Jack Smith ( if spouse is not a member)
>> 222 Cane Street
>> Utica, MI 48316
>>
>> How do I do this?
>>
>> Thank you
>>
>> Bill
>I did like Fred told
>[FirstName] & ("&"+ [Spouse Name])
>
>If the SpouseName is null the "&" will not print.
>
>--
>Fred
>Please only reply to this newsgroup.
>I do not reply to personal email.
Put the spaces around the & as below.

[FirstName] & (" & "+[SpouseName]) & " " & [LastName]

Jack & Jane Smith
Jack Smith

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.



--
Bob Quintal

PA is y I've altered my email address.


Nov 13 '05 #7

P: n/a
On Wed, 16 Feb 2005 13:38:02 GMT, Bill Smith wrote:
Bob , will this remove the & in the field. I am really a very basic
user and would like some more direction. Thanks

Bill

On Tue, 15 Feb 2005 22:36:52 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:
bs******@comcast.net (Bill Smith) wrote in
news:42***************@news.mi.comcast.giganews. com:
Great job Fred. It works fine. Now the only problem I have is
I have this very large database and under spouse I have a
"space&name".This is the only way I knew how to do it before
but I knew it was not right. Now I would like to go and clean
up my database by find and replace. I would like to find all
the "space&name" and remove the space& and just have the name.
Is there a way I can do it?

Thanks again.

Bill

create an update query. field one should be [spouse]. the
"replace with" for this column should be trim([spouse])

running that will remove all the leading spaces

Bob

On Tue, 15 Feb 2005 02:24:12 GMT, fredg
<fg******@example.invalid> wrote:

On Mon, 14 Feb 2005 23:08:10 GMT, Bill Smith wrote:

> When I did like Fred showed me
> =Trim([First Name] & " " & ("&"+[Spouse]) & " " & [Last
> Name] I need to get a space between the first name and the
> spouse.' It is reading as follows:
> Jack &Jane Smith
> 222 Cane Street
> Utica, MI 48316
>
> Thanks again.
>
> Bill
>
> On Mon, 14 Feb 2005 17:34:39 GMT, fredg
> <fg******@example.invalid> wrote:
>
>>On Mon, 14 Feb 2005 16:36:00 GMT, Bill Smith wrote:
>>
>>> I am working on a fairly simple database for our bike club
>>> where we keep records of the following:
>>> First Name: Spouse Name: Address
>>> I would like the labels to print the first name & spouse's
>>> name if they are a member and not have the & sign if their
>>> spouse is not a member.
>>>
>>> Example: Jack & Jane Smith
>>> 222 Cane Street
>>> Utica, MI 48316
>>>
>>> or Jack Smith ( if spouse is not a member)
>>> 222 Cane Street
>>> Utica, MI 48316
>>>
>>> How do I do this?
>>>
>>> Thank you
>>>
>>> Bill
>>I did like Fred told
>>[FirstName] & ("&"+ [Spouse Name])
>>
>>If the SpouseName is null the "&" will not print.
>>
>>--
>>Fred
>>Please only reply to this newsgroup.
>>I do not reply to personal email.
Put the spaces around the & as below.

[FirstName] & (" & "+[SpouseName]) & " " & [LastName]

Jack & Jane Smith
Jack Smith

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


--
Bob Quintal

PA is y I've altered my email address.


The fastest way to get the answer to this (and many other) questions
is to try it out yourself. Certainly less time consuming than
composing your message and waiting for a reply.

Create a Select query using the expression you were given in a new
column.
Does the result meet your requirements? If not, then it will not work
as you wish in an Update query.
If it does work as required, then convert the Select query to an
Update query and run it (after making a backup of your table, of
course).

You could also look up the TRIM function in VBA help to see what it
does.

"Returns a Variant (String) containing a copy of a specified string
without leading spaces (LTrim), trailing spaces (RTrim), or both
leading and trailing spaces (Trim)."

The suggested Trim([Spouse]) will not remove the &.

If EVERY name has the space& as the first 2 characters, then all you
need do is:
NewSpouse:Mid([[Spouse],3)

If some of the records do NOT contain the space& you can use the
Replace function, if your version of Access supports it.
Replace([Spouse]," &","")

You can look up Replace in VBA help.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.