Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-26-2012, 01:35 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default time field issue in mail merge

Hi,



I am merging data from an Access 2007 query into a Word 2007 doc, including 2 date/time fields. For one I have added a field switch to only display the data in the desired format (\@ "dddd dd MMMM yyyy"), and am trying to do the same with the second field to only display the time in the hh:mm format.

Unfortunately, it seems that whatever switch I try to use, the field still display as 30/12/1899 hh:mm:ss. I have also tried playing around with the field format in Acces, but this does not seem to change anything.

Would be grateful for any advice!

Aude
Reply With Quote
  #2  
Old 04-27-2012, 02:29 AM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 64bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Aude,

You should be able to use:
{MERGEFIELD MyDate \@ "DDDD DD MMMM YYYY"} for the date; and
{MERGEFIELD MyTime \@ "hh:mm:ss"} for the time; or
{MERGEFIELD MyDateTime \@ "DDDD DD MMMM YYYY hh:mm:ss"} for the date & time.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 04-27-2012, 06:16 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default

Hi Paul,

this is what I did in the first instance, and whilst it works for the date field, it doesn't for the time.. Not sure where the problem lies.

Thanks
Aude
Reply With Quote
  #4  
Old 04-27-2012, 02:33 PM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 64bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Aude,

In that case, how are your source data formatted? Are they actual times, or some other form of data?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-02-2012, 12:53 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default

Hi Paul,

The 2 fields are Access date/time fields, with no particular formatting (although I did try changing this to see if it would help or not)

Thanks
Aude
Reply With Quote
  #6  
Old 05-02-2012, 01:02 AM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 64bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

hi Aude,

So what do you get as the output if you don't have any date/time switches? What regional language settings are you using and what is the default time-format separator for these?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 05-03-2012, 12:47 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default

Hi Paul,

There's not formatting on the source data, but the output has got the field switches you recommended above. The language is set to English UK, and time separator is :

The output for the time is the one causing problem, no matter what formatting I try to apply to the source data or field switch in the mail merge, it systematically comes out as 30/12/1899 hh:mm:ss

Aude
Reply With Quote
  #8  
Old 05-03-2012, 02:34 AM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 64bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Aude,

But what does the mergefield return if there is no formatting?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 05-03-2012, 02:37 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default

Hi Paul,

The mergefield returns 30/12/1899 hh:mm:ss for both fields when nothing is applied. When I add the appropriate switch for the date field, {MERGEFIELD MyDate \@ "DDDD DD MMMM YYYY"}, I get exaclty what I want, but the time one does ot change regardless of what switch I use.

Thanks
Aude
Reply With Quote
  #10  
Old 05-03-2012, 03:10 AM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 64bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Aude,

What do you get if you format the time mergefield as:
\# 0.0000
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 05-03-2012, 04:01 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default

Hi Paul,

Just tried, and got exactly the same output as before: 30/12/1899 hh:mm:ss

Aude
Reply With Quote
  #12  
Old 05-03-2012, 04:43 AM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 64bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Aude,

I've tracked down the problem, but I'm not sure what you'll be able to do about it without changing both your existing data and your data input procedures for the future. It's all to do with the default year that your database assumes where there is no date present. Word doesn't handle dates earlier than 1/1/1901, but your data outputs 31/12/1899 (plus the time). As an interim measure, you could:
• do a Directory/Catalogue merge of the data;
• use Find/Replace to change all instances of 31/12/1899 to 1/1/1901;
• then use the merged Directory/Catalogue data as the source for the final mailmerge.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 05-03-2012, 05:29 AM
Aude Aude is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2007
Novice
time field issue in mail merge
 
Join Date: Jan 2012
Posts: 8
Aude is on a distinguished road
Default

Hi Paul,

thank you so much for helping me out with this! Think I might just convert the time field to text, as it seems to be the easiest option in the data source, and returns the desired output.

Thanks
Aude
Reply With Quote
  #14  
Old 05-18-2014, 02:22 AM
iAltair iAltair is offline time field issue in mail merge Windows XP time field issue in mail merge Office 2010 32bit
Novice
 
Join Date: May 2014
Posts: 2
iAltair is on a distinguished road
Default time field issue

Quote:
Originally Posted by macropod View Post
Hi Aude,

I've tracked down the problem, but I'm not sure what you'll be able to do about it without changing both your existing data and your data input procedures for the future. It's all to do with the default year that your database assumes where there is no date present. Word doesn't handle dates earlier than 1/1/1901, but your data outputs 31/12/1899 (plus the time). As an interim measure, you could:
• do a Directory/Catalogue merge of the data;
• use Find/Replace to change all instances of 31/12/1899 to 1/1/1901;
• then use the merged Directory/Catalogue data as the source for the final mailmerge.
Hi,

I have the same problem. I create a access database in access 2010 on windows 8. My date and time field after using (\@ "dd/MM/yyyy" and\@ "HH:mm") displays as i want. The problem starts when i transfer my access file to another PC using XP 32bit and office 2010. When i merge my data the date field is correct but my time fields is like this...31/12/1899 19:00:00. I would like some advise how to correct that, analytical and simple even in pm.

Thanks
Michael
Reply With Quote
  #15  
Old 05-18-2014, 02:28 AM
macropod's Avatar
macropod macropod is offline time field issue in mail merge Windows 7 32bit time field issue in mail merge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,763
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

The reply you quoted says how the issue can be addressed.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
time field issue in mail merge Mail Merge - If a field is black, take the data from the row below? mikeprent Mail Merge 5 01-05-2012 04:52 AM
time field issue in mail merge Use a mail merge Field as the final doc filename DaveBF Mail Merge 1 10-22-2011 10:16 PM
time field issue in mail merge HTML Content in Mail Merge Field Ilmater Mail Merge 1 09-04-2011 02:47 AM
time field issue in mail merge Mail merge erases field codes Medievalguy88 Mail Merge 1 08-11-2011 05:21 AM
Mail merge Field Code Manipulation macjnr Mail Merge 0 09-10-2009 11:37 AM


All times are GMT -7. The time now is 01:19 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft