I am trying to use a query from Access to run a mail merge. The data I wish to merge in includes a date. In the tables and queries of the database all dates are shown in British format (day/month/year) however when I merge in data it seems to automatically change this to American Forma (month/day/year). This happens even when it appears in the orginal query in long hand format (e.g. 13th August 2005). Do you know how I can resolve this?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using Access for Mail Merge - american date format
- Thread starter Llandeilo
- Start date
LittleSmudge
Programmer
Yes.
It's one of those well known bugs.
The problem is not actually with Access but with the JET database itself.
Access pays attention to the international settings on your machine and will accurately reflect those settings.
However JET totally ignores those settings and any Date parameters passed into JET will be assumed to be the confused MMddYY form unless that is impossible where only then does it try the logical ddMMYY format.
This leads to amazing and apparently random malfunctions in some applications.
The Solution is to always force the date format into "Medium Date" when passing dates to JET
So your Query string becomes something like
SELECT field1, field2, field3, DateField
FROM tblA INNER JOIN tblB
ON tbla.fieldA = tblB.fieldB
WHERE DateField BETWEEN Format([StartDate], "Medium Date")
AND Format([EndDate], "Medium Date")
'ope-that-'elps.
G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
It's one of those well known bugs.
The problem is not actually with Access but with the JET database itself.
Access pays attention to the international settings on your machine and will accurately reflect those settings.
However JET totally ignores those settings and any Date parameters passed into JET will be assumed to be the confused MMddYY form unless that is impossible where only then does it try the logical ddMMYY format.
This leads to amazing and apparently random malfunctions in some applications.
The Solution is to always force the date format into "Medium Date" when passing dates to JET
So your Query string becomes something like
SELECT field1, field2, field3, DateField
FROM tblA INNER JOIN tblB
ON tbla.fieldA = tblB.fieldB
WHERE DateField BETWEEN Format([StartDate], "Medium Date")
AND Format([EndDate], "Medium Date")
'ope-that-'elps.
G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
Hi,
If you have sorce field as data type, any display format you use, the default format in word mailmerge is always US. You need to extend MERGEFIELD field by adding formatting switch, see word help for details. There is a bug in international help file though, you have to use US formatting symbols (y, M, d, h, m, s), otherwise formatting fails.
combo
If you have sorce field as data type, any display format you use, the default format in word mailmerge is always US. You need to extend MERGEFIELD field by adding formatting switch, see word help for details. There is a bug in international help file though, you have to use US formatting symbols (y, M, d, h, m, s), otherwise formatting fails.
combo
- Status
Similar threads
- Locked
- Question
