Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-27-2017, 08:12 PM
JennEx JennEx is offline Help With Time Fields Windows XP Help With Time Fields Office 2013
Competent Performer
Help With Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Help With Time Fields

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.
Reply With Quote
  #2  
Old 04-27-2017, 08:20 PM
macropod's Avatar
macropod macropod is offline Help With Time Fields Windows 7 64bit Help With Time Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 04-28-2017, 08:08 AM
JennEx JennEx is offline Help With Time Fields Windows XP Help With Time Fields Office 2013
Competent Performer
Help With Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 04-28-2017, 03:13 PM
macropod's Avatar
macropod macropod is offline Help With Time Fields Windows 7 64bit Help With Time Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 04-29-2017, 09:25 AM
JennEx JennEx is offline Help With Time Fields Windows XP Help With Time Fields Office 2013
Competent Performer
Help With Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 04-29-2017, 03:31 PM
macropod's Avatar
macropod macropod is offline Help With Time Fields Windows 7 64bit Help With Time Fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 04-29-2017, 05:21 PM
JennEx JennEx is offline Help With Time Fields Windows XP Help With Time Fields Office 2013
Competent Performer
Help With Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help With Time Fields Odd Result In Combined Merged Time Fields JennEx Mail Merge 9 04-29-2017 08:58 AM
Help With Time Fields Employee Turnover by Department/Month split between Voluntary/Involuntary - Full-time/Part-time CPSmith Excel 1 03-26-2016 02:48 AM
Help With Time Fields Displaying FORMTEXT fields when merge fields are empty or null sfkHooper Mail Merge 6 01-19-2016 04:24 PM
How do you get the DATE and TIME fields to be included in an export to Excel? tpieples Outlook 0 02-18-2013 12:11 PM
Word 2010 - DOCX File with fields -> PDF with fields senglory Word 2 03-27-2012 10:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:07 PM.


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