Date format question

[scott]

Hi Everyone,

I don't know if this is the correct forum but I thought i'd ask. I have an
access DB with a function to automatically safe a table with fields i'm mail
merging into MS Word. Word looks at the table from the Access Database.

The export function all works, but the Date format is incorrect. I want it
in Long format (Tuesday, 2, May, 2006) which is how it's stored in the
table. When I open Word with the merged document, the date is in Short and
the format is American. 5/2/06. I want it to show as long format above.

Can someone help me with where I can change the formatting?

Thanks.

 

[Peter Jamieson]

Either
a. try changing the method Word uses to connect to Access (check Word
Tools|Options|General|"Confirm conversion at open", go through the
connection process again and check the DDE option when offered or
b. use Alt-F9 to reveal the field codes and change, e.g.

{ MERGEFIELD \*Mergeformat }

to

{ MERGEFIELD \@"DDD, DD, MMM, YYYY" \*Mergeformat }

There's more informaiton about these "format switches" in Word Help. If you
are connecting using OLEDB (the default method in Word XP/2003) test some of
your dates to ensure that the month and day are not reversed. If they are,
this is a result of an error in the OLEDB provider and you either need to
contact MS Customer Services for a "hotfix"

or

c. Create a query in Access that uses a format() function to create the
additional date format you need, and use that query as the source for your
merge.

Finally...

which is how it's stored in the table.

....it may look that way, but unless you're storing it as a string, it's
actually stored internally in a standard binary date/time format. Elsewhere
(e.g. in the Access table definitions or individual forms) you specify
formats used to verify what you enter in the field or how they are
displayed. In essence, OLEDB (and its predecessor ODBC) do not "see" that
formatting information and just get the underlying binary data.

Peter Jamieson

 

[scott]

Appreciate your help Peter. That worked a treat.

If I could puch my luck and ask for the time layout as well. I want to be
able to show the time in a 12hr format. Currently it shows as a date
(YYYY-MM-DD) followed by time in 24hr. All I want is it to show 4:00pm.

Thanks again.

 

[Peter Jamieson]

It's mostly in the Word help

{ MERGEFIELD mydatetime \@"YYYY-MM-DD h:mmam/pm" }

gets you an uppercase am or pm. If you really want lower case you would
probably have to do

{ MERGEFIELD mydatetime \@"YYYY-MM-DD h:mm"
}{ MERGEFIELD mydatetime \@"am/pm" \*lower }

Peter Jamieson

 

[macropod]

Hi Scott,

If you only want the 4:00pm to show, try:
{MERGEFIELD mydatetime \@ "hh:mmam/pm" \*lower}
or if you've got, say 4:35pm and you only want the 4:00pm to show, try:
{MERGEFIELD mydatetime \@ "hh:00am/pm" \*lower}

Cheers