English and American date format

[Contro]

Hi guys,

I have a database set up which allows you to put in dates in English
date format (dd/mm/yyyy), but I then went to set up a mail merge
document, and this displays the date in the American format
(mm/dd/yyyy). Is there any way to stop this? It's very odd, as the
date is fine in the database, so I don't know why it's changing the
dates upon merging to word.

Thanks for your help!

Contro.

 

[Allen Browne]

Internally, Access stores the dates as a number, where the whole number
represents the date, and the fraction the time of day (e.g. 0.5 = noon, 0.25
= 6am (one quarter of a day).)

This article describes how Access accepts and displays dates:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If the dates are then merged to Word, you need to get Word to format the
dates. You can set the format of the field in Word to indicate how the dates
should be displayed.

If you cannot get Word to handle the dates correctly, another option is to
explicitly format them as text in a query in Access, and then use the query
for the merge so Word does not have to format them. To do this in Access,
you would type something like this into the Field row in query design:
Format([MyDate], "Short Date")
replacing "MyDate" with the name of your field.

 

[Van T. Dinh]

The easiest way is to set the required date format in the relevant Merge
Field of your MailMerge document. When Access passes the DateTime data to
Word, it only passes the value and not the format.

Check Word Help on how to format a Merge Field.

Alternatively, you can create a Query with a calculated Field using the
Format() function to format the data value to the format you want. Note
that the Format() function returns a Variant of String type and hence the
exact formatted date will be accepted by Word as a String without
modification.

 

[Contro]

That's great! Thank you so much for your help! It's all perfect now.


Ta!

Contro.