Mail Merge - If statement

[crae]

Peter,

Thank you for the quick reply. Wow. Your suggestions and examples worked
well. Part of my hang up was not using the ctrl+F9 to create the brackets,
even before the IF word. You said in your reply to watch out for this.
Rightly so.

I'm not getting the exact data qty I was expecting but will look into that
some more on my end (atleast it IS stating 0 which was my default if the
criteria wasn't met). You have my formula working well - all parts are in
place. Thank you Peter.

Can I ask another question? Within this merge letter, there is a merge
field titled Due_Date. I'm expecting the data to come in with the following
format 12/22/08. Currently it's displaying 2009-06-16 00:00:00
The Due Date field in the Access Database is not formatted like that, why
does it display in Word like that?

Thanks for all your time/trouble. You've already made my holidays happier.
.. .I'd been wrestling with that syntax for a week now. It must be fun to
know so much about particular things.

crae

 

[Peter Jamieson]

Can I ask another question? Within this merge letter, there is a merge

field titled Due_Date. I'm expecting the data to come in with the following
format 12/22/08. Currently it's displaying 2009-06-16 00:00:00
The Due Date field in the Access Database is not formatted like that, why
does it display in Word like that?

There are a number of things to bear in mind when it comes to formatting.

First, Access itself really has two parts - the Access "front end",
which really handles presentation, forms, reports etc., and the
"database engine", which stores and retrieves data. In a simple .mdb,
the database engine is Jet (or ACE in an Access 2007 .accdb), although
Access can use other engines in various ways.

So when you define a date column in an Access table, Jet itself
(probably) will store the column as a date or datetime column, and it
probably does not know anything about the display format you have
defined - i.e. although the formats are defined in Jet tables in the
..mdb, Jet does not know what that data means - only the Access frontend
knows.

(Actually I don't know that that is exactly how Access/Jet treats the
data but it will be something like that)

Second, Word has several different methods for connecting to Access data
- DDE, ODBC, and in Word 2002 and later, OLE DB. DDE was the default
method in Word 2000 and earlier, and OLE DB is the default method in
Word 2002 and later. DDE works by starting Access (if necessary), then
getting Access to open the database and retrieve the data on behalf of
Word. So when Word is using DDE, you may find that data is being
formatted according to the rules that the front end knows about (I can't
remember in this particular case). However, when you use ODBC or OLE DB,
Word does not use Access at all - it uses the Jet or ACE engine
directly. So what you get is what that engine returns.

Typically you can alter the format returned by using a date format
switch in your Word mailmerge document, e.g.

{ MERGEFIELD mydate \@"MM/DD/YYYY" }

for typical U.S. date format. However, it is as well to ensure that
a. month and day are not reversed in this situation when they are
ambiguous, e.g. 6th July, 7th June
b. null dates are not appearing as if they are real dates.

If you have trouble with these date switches not working, the simplest
fix is probably to write a query in Access that formats the date as a
text string with either the exact format you want, or is formatted as
YYYY-MM-DD . In the latter case, Word's date switches should work as you
expect.

Season's greetings,

Peter Jamieson

http://tips.pjmsn.me.uk