Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / May 2006

Tip: Looking for answers? Try searching our database.

Date format question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
scott - 02 May 2006 07:49 GMT
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 - 02 May 2006 08:21 GMT
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

> Hi Everyone,
>
[quoted text clipped - 11 lines]
>
> Thanks.
scott - 02 May 2006 10:19 GMT
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.

> Either
> a. try changing the method Word uses to connect to Access (check Word
[quoted text clipped - 51 lines]
>>
>> Thanks.
Peter Jamieson - 02 May 2006 10:58 GMT
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

> Appreciate your help Peter. That worked a treat.
>
[quoted text clipped - 59 lines]
>>>
>>> Thanks.
macropod - 02 May 2006 22:25 GMT
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

> Appreciate your help Peter. That worked a treat.
>
[quoted text clipped - 59 lines]
> >>
> >> Thanks.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.