#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
Hi Aude,
In that case, how are your source data formatted? Are they actual times, or some other form of data?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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 |
#8
|
||||
|
||||
Hi Aude,
But what does the mergefield return if there is no formatting?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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 |
#10
|
||||
|
||||
Hi Aude,
What do you get if you format the time mergefield as: \# 0.0000
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Hi Paul,
Just tried, and got exactly the same output as before: 30/12/1899 hh:mm:ss Aude |
#12
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
time field issue
Quote:
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 |
#15
|
||||
|
||||
The reply you quoted says how the issue can be addressed.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge - If a field is black, take the data from the row below? | mikeprent | Mail Merge | 5 | 01-05-2012 04:52 AM |
Use a mail merge Field as the final doc filename | DaveBF | Mail Merge | 1 | 10-22-2011 10:16 PM |
HTML Content in Mail Merge Field | Ilmater | Mail Merge | 1 | 09-04-2011 02:47 AM |
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 |