Access 2k -> Word 2k merge question: COMPARE mergefields and output text + mergefield data as result

[Neil Marsh]

Hi again. You folks were able to help me before, so I'll give it another shot. I hope I'm able to explain this clearly enough...

I'm writing a book about a radio series from the early 1980s and I've just finished collating all the data regarding each episode (title, writer, cast, crew, air dates, tape releases, etc.) in an Access 2k database that I built. I'm using merge fields in Work 2k to format the data neatly into the pages that will form the book's episode guide. I have almost all of the fields worked out so that lines for sub-headings where there is data will print and lines for sub-headings where there is no data will not. Switching from record to record under VIEW MERGED DATA looks very neat and clean, with the exception of one field, which I think will have to use a more complex formula involving IF...THEN...ELSE and COMPARE. Unfortunately, the only examples I have been able to find for COMPARE involve single text strings as the possible output and what I need to output are combined text strings and mergefields.

I'll try to illustrate the setup I have with a mocked-up entry:

THE TEST EPISODE FROM HELL #1
by Neil Marsh
Based on "The Possessed Broadcast" (1980), by Bill Howell & William Lane
Genre: Horror, Suspense, Mystery Length: 30:00
Produced at CBC Toronto Recorded on Mar 4, 80 & Mar 5, 80
Original Air Date: Jul 4, 80 Repeat(s): Feb 11, 81 & Aug 31, 82

The fields in blue (title, #, author, genre, length, produced at, recorded on, and original air date) always print because there will always be data for them, while the fields in red (based on and repeat) print only if there is data to put in them.

Now here is where I'm having a problem. The next two fields can end up in one of four possible combinations:

1. DATA - DATA
2. DATA - NODATA
3. NODATA -DATA
4. NODATA - NODATA

And the results would appear like this on the printed page, respectively:

1. FM Repeat: Dec 15, 80 Arts Nat'l Air Date: Apr 12, 84
2. FM Repeat: Dec 15, 80
3. Arts Nat'l Air Date: Apr 12, 84
4.

In other words, the layout of this line is dependent upon the content of the fields in the database. So, in order to keep the data all on the same line (yes, I know I could do this on separate lines and save myself the headache, but space is at a premium, so I need to condense as much as I can), I created a series of IF...THEN...ELSE COMPARE formulae:

v--This one checks to see if both fields are TRUE (the original Access fields are YES/NO checkboxes returning either TRUE or FALSE):

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = TRUE }, { COMPARE { MERGEFIELD ArtsNatl } = TRUE ) } = 1 "FM Repeat: { MERGEFIELD CBCFMRepeat \@ "MMM d, yy" } Arts Nat'l Air Date: { MERGEFIELD ANAirDate \@ "MMM d, yy" }" }

If both fields are TRUE, then both text label/mergefield combinations should appear on the line:

FM Repeat: Dec 15, 80 Arts Nat'l Air Date: Apr 12, 84

------------

v--This one checks to see if the first field is TRUE and the second one FALSE:

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = TRUE }, { COMPARE { MERGEFIELD ArtsNatl } = FALSE ) } = 1 "FM Repeat: { MERGEFIELD CBCFMRepeat \@ "MMM d, yy" }

If the first field is TRUE and the second one FALSE, then this is how the data should appear on the line:

FM Repeat: Dec 15, 80

------------

v--This one checks to see if the first field is FALSE and the second one TRUE:

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = FALSE }, { COMPARE { MERGEFIELD ArtsNatl } = TRUE ) } = 1 "Arts Nat'l Air Date: { MERGEFIELD ANAirDate \@ "MMM d, yy" }

If the first field is FALSE and the second one TRUE, then this is how the data should appear on the line:

Arts Nat'l Air Date: Apr 12, 84

------------

v--And lastly, this one checks to see if both fields are FALSE:

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = FALSE }, { COMPARE { MERGEFIELD ArtsNatl } = FALSE ) } = 1 "" }

If both fields are FALSE, then there should be nothing: no text and no new line.

------------

However, I am not getting the results I want. These combinations return *nothing* on any of the records I've created to test them: no label text, no mergefield data, nada. Completely blank lines. I'm quite sure there's a simple explanation (isn't there always?), but I'm not seeing it yet.

** Yes, I used ALT-F9 to create the brackets **

Can anyone at least give me a nudge in the right direction?

Thanks!

-/\/eil

---
Neil Marsh * (e-mail address removed)
Cambridge, MA * http://AudioBoy.net

**********************************************************************
"In the dream you are falling, lost in the listening distance as
dark locks in ... Nightfall!"

-- Opening narrative to "Nightfall"
CBC Radio horror anthology, 1980-1983
**********************************************************************

 

[Peter Jamieson]

Discover what the result of { MERGEFIELD FMRepeat } looks like. It /might/
be "True" or "False", in which case your comparison needs to be with the
string, e.g. { MERGEFIELD FMRepeat } = "True" . But depending on how you
connect to the data it may also be 0, 1 or -1, where 0 is "False". So you
may need { MERGEFIELD FMRepeat } <> 0.

If you want, you can use a nested IF field and avoid the COMPARE fields,
e.g.

{ IF { MERGEFIELD FMRepeat } = 0
"{ IF { MERGEFIELD ArtsNatl } = 0
""
"Arts Nat'l Air Date: { MERGEFIELD ANAirDate \@ "MMM d, yy" }" }"
"{ IF { MERGEFIELD ArtsNatl } = 0
"FM Repeat: { MERGEFIELD CBCFMRepeat \@ "MMM d, yy" }"
"FM Repeat: { MERGEFIELD CBCFMRepeat \@ "MMM d, yy" } Arts Nat'l Air Date:
{ MERGEFIELD ANAirDate \@ "MMM d, yy" }" }

(Hope I've got all my {} and "" in the right places!)

** Yes, I used ALT-F9 to create the brackets **

I'm assuming you mean ctrl-F9

--
Peter Jamieson
MS Word MVP

Hi again. You folks were able to help me before, so I'll give it another
shot. I hope I'm able to explain this clearly enough...

I'm writing a book about a radio series from the early 1980s and I've just
finished collating all the data regarding each episode (title, writer, cast,
crew, air dates, tape releases, etc.) in an Access 2k database that I built.
I'm using merge fields in Work 2k to format the data neatly into the pages
that will form the book's episode guide. I have almost all of the fields
worked out so that lines for sub-headings where there is data will print and
lines for sub-headings where there is no data will not. Switching from
record to record under VIEW MERGED DATA looks very neat and clean, with the
exception of one field, which I think will have to use a more complex
formula involving IF...THEN...ELSE and COMPARE. Unfortunately, the only
examples I have been able to find for COMPARE involve single text strings as
the possible output and what I need to output are combined text strings and
mergefields.

I'll try to illustrate the setup I have with a mocked-up entry:

THE TEST EPISODE FROM HELL #1
by Neil Marsh
Based on "The Possessed Broadcast" (1980), by Bill Howell & William Lane
Genre: Horror, Suspense, Mystery Length: 30:00
Produced at CBC Toronto Recorded on Mar 4, 80 & Mar 5, 80
Original Air Date: Jul 4, 80 Repeat(s): Feb 11, 81 & Aug 31, 82

The fields in blue (title, #, author, genre, length, produced at, recorded
on, and original air date) always print because there will always be data
for them, while the fields in red (based on and repeat) print only if
there is data to put in them.

Now here is where I'm having a problem. The next two fields can end up in
one of four possible combinations:

1. DATA - DATA
2. DATA - NODATA
3. NODATA -DATA
4. NODATA - NODATA

And the results would appear like this on the printed page, respectively:

1. FM Repeat: Dec 15, 80 Arts Nat'l Air Date: Apr 12, 84
2. FM Repeat: Dec 15, 80
3. Arts Nat'l Air Date: Apr 12, 84
4.

In other words, the layout of this line is dependent upon the content of the
fields in the database. So, in order to keep the data all on the same line
(yes, I know I could do this on separate lines and save myself the headache,
but space is at a premium, so I need to condense as much as I can), I
created a series of IF...THEN...ELSE COMPARE formulae:

v--This one checks to see if both fields are TRUE (the original Access
fields are YES/NO checkboxes returning either TRUE or FALSE):

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = TRUE }, { COMPARE {
MERGEFIELD ArtsNatl } = TRUE ) } = 1 "FM Repeat: { MERGEFIELD CBCFMRepeat \@
"MMM d, yy" } Arts Nat'l Air Date: { MERGEFIELD ANAirDate \@ "MMM d,
yy" }" }

If both fields are TRUE, then both text label/mergefield combinations should
appear on the line:

FM Repeat: Dec 15, 80 Arts Nat'l Air Date: Apr 12, 84

------------

v--This one checks to see if the first field is TRUE and the second one
FALSE:

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = TRUE }, { COMPARE {
MERGEFIELD ArtsNatl } = FALSE ) } = 1 "FM Repeat: { MERGEFIELD CBCFMRepeat
\@ "MMM d, yy" }

If the first field is TRUE and the second one FALSE, then this is how the
data should appear on the line:

FM Repeat: Dec 15, 80

------------

v--This one checks to see if the first field is FALSE and the second one
TRUE:

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = FALSE }, { COMPARE {
MERGEFIELD ArtsNatl } = TRUE ) } = 1 "Arts Nat'l Air Date: { MERGEFIELD
ANAirDate \@ "MMM d, yy" }

If the first field is FALSE and the second one TRUE, then this is how the
data should appear on the line:

Arts Nat'l Air Date: Apr 12, 84

------------

v--And lastly, this one checks to see if both fields are FALSE:

{ IF { = AND ( {COMPARE { MERGEFIELD FMRepeat } = FALSE }, { COMPARE {
MERGEFIELD ArtsNatl } = FALSE ) } = 1 "" }

If both fields are FALSE, then there should be nothing: no text and no new
line.

------------

However, I am not getting the results I want. These combinations return
*nothing* on any of the records I've created to test them: no label text, no
mergefield data, nada. Completely blank lines. I'm quite sure there's a
simple explanation (isn't there always?), but I'm not seeing it yet.

** Yes, I used ALT-F9 to create the brackets **

Can anyone at least give me a nudge in the right direction?

Thanks!

-/\/eil

---
Neil Marsh * (e-mail address removed)
Cambridge, MA * http://AudioBoy.net

**********************************************************************
"In the dream you are falling, lost in the listening distance as
dark locks in ... Nightfall!"

-- Opening narrative to "Nightfall"
CBC Radio horror anthology, 1980-1983
**********************************************************************

 

[Neil Marsh]

Discover what the result of { MERGEFIELD FMRepeat } looks like. It /might/
be "True" or "False", in which case your comparison needs to be with the
string, e.g. { MERGEFIELD FMRepeat } = "True" . But depending on how you
connect to the data it may also be 0, 1 or -1, where 0 is "False". So you
may need { MERGEFIELD FMRepeat } <> 0.

If you want, you can use a nested IF field and avoid the COMPARE fields,
e.g.

{ IF { MERGEFIELD FMRepeat } = 0
"{ IF { MERGEFIELD ArtsNatl } = 0
""
"Arts Nat'l Air Date: { MERGEFIELD ANAirDate \@ "MMM d, yy" }" }"
"{ IF { MERGEFIELD ArtsNatl } = 0
"FM Repeat: { MERGEFIELD CBCFMRepeat \@ "MMM d, yy" }"
"FM Repeat: { MERGEFIELD CBCFMRepeat \@ "MMM d, yy" } Arts Nat'l Air Date:
{ MERGEFIELD ANAirDate \@ "MMM d, yy" }" }

(Hope I've got all my {} and "" in the right places!)

You were missing an additional " } at the very end, but otherwise it was
fine...

Thanks for the ideas, Peter. Unfortunately I couldn't make either of
these work. I tried every combination of TRUE, "True", = -1, <>0, = 0,
etc., that I could think of, but I couldn't get it to work in all four
instances. I got it to work when the results were TRUE FALSE, FALSE
TRUE, and FALSE FALSE, but never TRUE TRUE. So I gave that up and went
with having Word check the actual data fields themselves (instead of the
True/False checkboxes), to see if they are empty or not. This worked
perfectly, but it's not the way I want to do things, because some of
those fields may be empty because I haven't found the data to fill them
yet, whereas the checkboxes tell me whether or not they're *supposed* to
be filled in. I guess I could go through the database and replace the
checkboxes with actual values of Y and N. That would certainly work,
though it defeats the whole purpose of having the checkboxes in the
first place :/

 

[Peter Jamieson]

I got it to work when the results were TRUE FALSE, FALSE

TRUE, and FALSE FALSE, but never TRUE TRUE

Odd. Maybe I got somethign the wrong way around. If you just insert {
MERGEFIELD FMRepeat } and { MERGEFIELD ArtsNatl } into the merge main
document, what results do they display for TRUE TRUE?

So I gave that up and went
with having Word check the actual data fields themselves (instead of the
True/False checkboxes), to see if they are empty or not. This worked
perfectly, but it's not the way I want to do things, because some of
those fields may be empty because I haven't found the data to fill them
yet, whereas the checkboxes tell me whether or not they're *supposed* to
be filled in.
Understood.

I guess I could go through the database and replace the
checkboxes with actual values of Y and N. That would certainly work,
though it defeats the whole purpose of having the checkboxes in the
first place :/

Instead, you could define an Access query to do that, e.g. something like

SELECT iif(FMRepeat,'Y','N') AS `FMRepeatYN`, iif(ArtsNatl,'Y','N') AS
`ArtsNatlYN`, * FROM mytable

Or as long as you don't need to format any of the words etc. you could just
do the whole thing in the query

SELECT
'FM Repeat: ' + format(CBCFMRepeat,'MMM d, yy') AS `FMRD`,
'Arts Nat''l Air Date: ' + format(ANAirDate,'MMM d, yy') AS `ANAD`,
iif(FMRepeat,
iif(ArtsNatl,FMRD + ' ' + ANAD,FMRD),
iif(ArtsNatl,ANAD,'')
) AS `mytext`, * FROM mytable

and use { MERGEFIELD mytext } in yout merge document.