American/UK date formats

[GaryRW]

I posted a query earlier about mailmerges and blank date fields being populated with the current date, but I've since discovered an equally annoying problem.

I'm using access in the UK to run mailmerges in word. In Word 2000, this worked fine by setting the regional settings of the machine to the UK date format of dd/mm/yyyy. But in word 2002 (running on Windows XP), this doesn't work. Linking to exactly the same database entry, and using the same mailmerge document, on all the machines I've been running this set-up up to now, it has worked fine and still does. On the new user's machine, it comes up in US format, unless it doesn't make sense as a date e.g.

1/9/02 (meaning 1st Sept 2002) displays as 9 January 2002.

but 25/10/02 (meaning 25 Oct 2002) displays as 25 Oct 2002.

Is there anything other than the regional settings that need changing? Any other ideas?

Your help would be much appreciated

 

[mdav2]

I found this using Access 97 and Access 2000. I had a date mask and format on a field that forces the user to enter the date in the English date format (dd/mm/yyyy). However, on some machines the dates were going all American.

The reason is to do with the machines regional settings, as you mentioned. The date masks and formats are ignored/overwritten by the machines regional settings. If these are set to US then the date will appear wrong, even if input in the format you want. The most annoying part is that you have to change this on each users PC and once changed you then dates can become wrong too.

 

[GaryRW]

Thanks Mdav.

My confusion runs from the fact that both computers' regional settings are set to UK. I was tearing my hair out about this originally, but I finally got Access and Word and the regional settings all agreeing with each other, and it all worked lovely with everything stored and appearing in UK format.

Now on the new machine running XP, the regional settings have been set to UK, but I am having the same problem as I did orginally. The dates are all held in the access file in UK format, but then Word seems to be trying to convert them to US, even though the computer is set to use UK dates!! Aggggh.

 

[onedtent]

Not sure if this will fix it, but check to see is US English is chosen under Regional Options, Input locales tab.

 

[GaryRW]

input locales tab?

I have checked on the regional settings tab on the Control Panel and even in the dictionary setting in Word. All set to UK English (mores the pity).

is this what you meant ondedtent?

 

[GaryRW]

at the start of this thread, I mentioned another thread about a related annoying problem which is either taxing people too much (or more likely) slipped everybody by...

The problem is that I have a system set up in office 2000 using access to run mail merges. In Word 2000, if a date field in the database is blank, then the merge field is blank (sounds sensible to me). I then have IF fields which ensure the letter makes sense if the field is blank.

BUT

I have a new user who is running Word 2002. On her machine, if a date field is blank in the database, the mergefield linked to that blank field will instead insert today's date. Hence all my lovingly created IF fields checking to see if the date is blank or not do not work and letters are produced that don't make sense.

I have trawled through Word help but can find nothing about this - I really hope that there is an option about this that I am missing, if not, can anybody suggest anything?

Many thanks for the continued inspired help that people give (I have tried the MS Newsgroups but never receive a response. )

 

[onedtent]

Found the answer to this thread, this may also cure your other problem:

Word 2002 uses OLEDB to link to the data - 2000 and versions before used DDE, (connecting directly to the other application) and as such picked up your original formatting.

to use the old technology
Word: Tools/Options/General
activate "Confirm conversions on open"
Link to your data source
a new dialog box with a list of connection
methods pops up. Choose DDE
- proceed normally

 

[GaryRW]

onedtent, you are a genius

That solved both problems. However (didn't you know that there was a 'however' ) I haven't been using DDE, I was using ODBC since that was much quicker.

Thanks to your help, I can now make it work manually, but do you know how to force Word to use ODBC in code? For info, my current code that links to the access database is:

With Precedent_Name.MailMerge
.OpenDataSource Name:= _
Path, ConfirmConversions:=False, ReadOnly:=False _
, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
"", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"DSN=MS Access Database;DBQ=" & Path & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:=SQL_State, SQLStatement1:=SQL_State2
.Execute Pause:=True
End With

With Path being the location of the database, and SQL_State and SQL_State2 giving the WHERE clause to use. Using that, even with the Confirm Conversions box ticked, Word still seems to be using OLEDB.

Is there any way to choose the linking method in VBA?

Thanks for all the help so far,

 

[GaryRW]

For once I managed to find the answer on the MS web site:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q285332

Just in case anyone else was following the thread.

Thanks for your inpiration onedtent!