Blank fields in merge

[hkbs]

Word 2000/Access 2000

I have all blank lines suppressed but cannot find in Help how to suppress
fields.

I have the following fields:
Title, Firstname, Lastname

If the first two are blank, I get two spaces before the Lastname.

How do I get rid of these, please.

TIA
hkbs

 

[Peter Jamieson]

You need a nested IF field, e.g. if any of the fields could be blank you
might have

{ IF "{ MERGEFIELD Title }" = ""
"{ IF "{ MERGEFIELD Firstname }" = ""
""
"{ MERGEFIELD Firstname } " }"
"{ MERGEFIELD Title } { IF "{ MERGEFIELD Firstname }" = ""
""
"{ MERGEFIELD Firstname } "
}" }{ MERGEFIELD Lastname }

where all the {} are inserted using ctrl-F9

 

[hkbs]

Thanks Peter, but I'm a bit lost!
I probably did not give enough info.

I am doing a mail merge to a main document which I have set up as a template
for small labels (24 to an A4 sheet = 3 wide 8 down).

My data source is an Access query. The full fields are:

«Title» «Intial» «Last_name»
«Address1»
«Address2»
«Town»
«County»
«Postcode»

I am then using a straight 'Merge to new document' with the aid of the
mailmerge helper.
Some of my contacts do not have a title/initial (e.g. name of a club).

Do I have to put the nested fields in each label? It would make the small
labels a bit crowded!

I have searched the Help files and tried to get to an on-line site where I
can search archives - without success!

Any help would be greatly appreciated.

hkbs
-------------------

 

[Peter Jamieson]

Do I have to put the nested fields in each label?
Yes.
It would make the small
labels a bit crowded!

If you set up the nested field in a blank document (it doesn't have to be
connected to a data source) then copy/paste them into each cell instead of
the existing fields you should be OK. You can put all the fields in one
line - I just lay them out over several rows to make it clearer.

My data source is an Access query. The full fields are:

Well, if you are in a position to modify the query, you can do all the
necessary work in there. In SQL you would need something like

SELECT iif(title = '',iif(initial='','',initial + ' '),title + ' ' +
iif(initial='','',initial + ' ')) + last_name AS `fullname`, * FROM
yourquery

 

[hkbs]

SELECT iif(title = '',iif(initial='','',initial + ' '),title + ' ' +

iif(initial='','',initial + ' ')) + last_name AS `fullname`, * FROM
yourquery

Thanks again but I am not an expert on Access. I create queries by design or
wizard. I have just had a look at View | SQL. At present it shows:

SELECT [Table 1].Title, [Table 1].Intial, [Table 1].Last_name, [Table
1].Address1, [Table 1].Address2, [Table 1].Town, [Table 1].County, [Table
1].Postcode
FROM [Table 1]
WHERE ((([Table 1].[Send card])=Yes))
ORDER BY [Table 1].Last_name;

What exactly do I need to paste in? Or can I enter something in Design view?
I have some contacts with no Title and/or no Initial. I know how to put "is
(or is not)
null" in either of these fields but not how to enter either/or to cover
both fields.

Not sure if I am explaining this very well!

hkbs

 

[Peter Jamieson]

Thanks again but I am not an expert on Access. I create queries by design
or

wizard.

Understood. However, I'm not an expert on Access either, which is why I tend
to use the SQL view

So in this case you could try creating a new query (just so you don't screw
up your existing one) and pasting the following SQL

SELECT iif([Table 1].Title = '',
iif([Table 1].Initial='','',[Table 1].Initial + ' '),[Table 1].Title + ' ' +
iif([Table 1].Initial='','',[Table 1].Initial + ' ')) + [Table 1].Last_name
AS `Full_name`,
[Table 1].Title, [Table 1].Initial, [Table 1].Last_name,
[Table 1].Address1, [Table 1].Address2, [Table 1].Town,
[Table 1].County, [Table 1].Postcode
FROM [Table 1]
WHERE ((([Table 1].[Send card])=Yes))
ORDER BY [Table 1].Last_name;

There is no significance in the layout (i.e. location of the line breaks) in
the text - it can all be on one line, as long as the strings with a single
blank (' ') appear correctly in your query text.

I've used the name "Initial" above, whereas your sample text shows "Intial",
which could be a typo - if not, just change all my "Initial" s to "Intial" s

If that query works, for future reference you should be able to switch to
Access's graphical query definition view and see how it would expect you to
define the function in that view.

--
Peter Jamieson
MS Word MVP

SELECT iif(title = '',iif(initial='','',initial + ' '),title + ' ' +
iif(initial='','',initial + ' ')) + last_name AS `fullname`, * FROM
yourquery

Thanks again but I am not an expert on Access. I create queries by design or
wizard. I have just had a look at View | SQL. At present it shows:

SELECT [Table 1].Title, [Table 1].Intial, [Table 1].Last_name, [Table
1].Address1, [Table 1].Address2, [Table 1].Town, [Table 1].County, [Table
1].Postcode
FROM [Table 1]
WHERE ((([Table 1].[Send card])=Yes))
ORDER BY [Table 1].Last_name;

What exactly do I need to paste in? Or can I enter something in Design view?
I have some contacts with no Title and/or no Initial. I know how to put "is
(or is not)
null" in either of these fields but not how to enter either/or to cover
both fields.

Not sure if I am explaining this very well!

hkbs

 

[hkbs]

I haven't got it right yet - but will try again when I have a bit more time
to study the subject!

Thanks once again, Peter

I've used the name "Initial" above, whereas your sample text shows "Intial",
which could be a typo - if not, just change all my "Initial" s to "Intial"

s

BTW it was a typo

Thanks again but I am not an expert on Access. I create queries by

design

or
wizard.

Understood. However, I'm not an expert on Access either, which is why I tend
to use the SQL view

So in this case you could try creating a new query (just so you don't screw
up your existing one) and pasting the following SQL

SELECT iif([Table 1].Title = '',
iif([Table 1].Initial='','',[Table 1].Initial + ' '),[Table 1].Title + ' ' +
iif([Table 1].Initial='','',[Table 1].Initial + ' ')) + [Table 1].Last_name
AS `Full_name`,
[Table 1].Title, [Table 1].Initial, [Table 1].Last_name,
[Table 1].Address1, [Table 1].Address2, [Table 1].Town,
[Table 1].County, [Table 1].Postcode
FROM [Table 1]
WHERE ((([Table 1].[Send card])=Yes))
ORDER BY [Table 1].Last_name;

There is no significance in the layout (i.e. location of the line breaks) in
the text - it can all be on one line, as long as the strings with a single
blank (' ') appear correctly in your query text.

I've used the name "Initial" above, whereas your sample text shows "Intial",
which could be a typo - if not, just change all my "Initial" s to "Intial" s

If that query works, for future reference you should be able to switch to
Access's graphical query definition view and see how it would expect you to
define the function in that view.

--
Peter Jamieson
MS Word MVP

Thanks again but I am not an expert on Access. I create queries by

design

or
wizard. I have just had a look at View | SQL. At present it shows:

SELECT [Table 1].Title, [Table 1].Intial, [Table 1].Last_name, [Table
1].Address1, [Table 1].Address2, [Table 1].Town, [Table 1].County, [Table
1].Postcode
FROM [Table 1]
WHERE ((([Table 1].[Send card])=Yes))
ORDER BY [Table 1].Last_name;

What exactly do I need to paste in? Or can I enter something in Design view?
I have some contacts with no Title and/or no Initial. I know how to put "is
(or is not)
null" in either of these fields but not how to enter either/or to cover
both fields.

Not sure if I am explaining this very well!

hkbs