Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
My club use a MS Access database to administer member info. Part of this involves printing membership cards with expiry date, and mail merging to letters with expiry date reminders.
I can only get these to appear in US format (mm/dd/yy) and cant find any way of changing it to UK format in the mail merge. Any suggestions how to do this? Currently I'm going through after the merge and manually changing the dates round.
I think access drives off your local machines language settings, can you see if you are on english-uk or english - us ??
It is also under (2010) file > options> language in access
p.s. im no expert on access, i work with the big boys toys! SQL server
Use a DB query for the merge source and in the date field use something like:
[code]DateValue(the_expiry_date) AS EXPIRYDATE[/code]
It may need some post-formatting, but it should no longer be American
If you are merging to Word rather than to a report are you sure that the problem isn't in the Word template? A date is pretty much a date until it is formated for display (except in SQL of course)
If you are merging to a report then it should pick up the regional settings for the computer you are running it on. This is often the problem when you move a database from one computer to a new one
PC is set to English(UK). The dates are in the correct format in the Access table. I can't find anything in word to format the date field in the document.
Try this:
Open the Word doc that you merge to
Click on the date field
Alt-F9 to view the mergefield code. It probably looks like this
{MERGEFIELD "expirydate"}
Change it to look like this
{MERGEFIELD "expirydate" \@ "dd\MM\yyyy"}
Mailmerge from a query rather than direct from the table. Use the format function in the query to sort the date for you:
=FORMAT(<mydate>,"DD/MM/YYYY")