I have two mergefields intended to display time values from my Excel data source.


Rather than displaying the times as they are in the database, they are displaying in the merged document as simply 12:00 AM. In the mail merge preview mode though, the times are correctly represented, but other time fields on my report are in decimal. When I merge, I get the 12:00A thing happening.

Here is a textual representation of my mailmerge field in which I am having difficulty with...

{QUOTE{SET OnTime {MERGEFIELD LIGHTS_ON_TIME} } {IF{= OnTime}<> {REF OnTime \* MERGEFORMAT } "N/A""{SET ss{= OnTime*86400 \# 0}}{SET hr{IF{ss}= 0 0 {= INT(ss/3600) \# 0}}}{SET mn{IF{ss}= 0 0 {= INT((ss-hr*3600)/60) \# 0}}}{SET AMPM{IF{hr}< 12 "AM" "PM"}}{= MOD({hr}+11,12)+1 \# 0}:{mn \# 00} {REF AMPM \* MERGEFORMAT }"} }

This has been carried over from a previous version of this report, so sadly I can honestly ay I'm unsure of how it works. I'm not sure what the N/A is all about. In earlier versions, the

The data in my worksheet from which the value for Lights_on_time is a time value (eg 9:00:00 PM) formatted as 9:00 PM.

I am simply looking to repair the formula I already have, or find a suitable alternative. I have tried a simple switch { MERGEFIELD LIGHTS_ON_TIME \@"h:mm AM/PM"} but I get the same behaviour as the merge field above. Proper representation of time in the mail merge document preview, but the merge document is 12:00 AM.

ISTR this field code was required because you had the Excel workbook open when the merge was done, and that was messing with the formats. The 'N/A' was for those cases when the lights would not be used.

Try:

{IF{MERGEFIELD LIGHTS_ON_TIME}= "" "N/A" {MERGEFIELD LIGHTS_ON_TIME \@ "h:mm AM/PM"}}

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you Paul. I replaced my merge field with your's but I am still getting 12:00 PM in this field.

So I am clear, should the Excel workbook be open or closed when merging? I get frustrated when a report can work one time, but not another. For instance, just now when testing your latest suggestion, the Page {PAGE} of {NUMPAGES} field in my footer has stopped working. In some reports no problem, but now, problem.

Is there a way the mail merge document should be saved? Alt-F9 view? Preview mode? Does it make a difference? Maybe thats where I am going wrong.

Try:

{IF{MERGEFIELD LIGHTS_ON_TIME}= "12:00 PM" "N/A" {MERGEFIELD LIGHTS_ON_TIME \@ "h:mm AM/PM"}}

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I regret this still remains a problem.
I find it very odd that the times show up ok in the mail merge document, but in the merged document, they insist on just being 12:00 PM.

If it helps ... I use { MERGEFIELD LIGHTS_ON_TIME \@"h:mm AM/PM" } in other very similar reports which as I recall does not result in the same problem. Seems document specific?

So it's all the output from this field that's showing as 12:00 PM? Even when not using the IF test? If so, that's most likely because the data contain something other than times alone in that field.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi ya Paul.
After a lot of tinkering, I feel confident I resolved the problem. I had two instances of the same mail merge field. The one that was being used was one I didn't know existed. I had updated the code only on the one, but not both.