Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2017, 12:29 PM
JennEx JennEx is offline Odd Result In Combined Merged Time Fields Windows XP Odd Result In Combined Merged Time Fields Office 2013
Competent Performer
Odd Result In Combined Merged Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Odd Result In Combined Merged Time Fields

I am having great difficulty in displaying a mail merge field properly in the merged document. Time data is taken from two cells of an Excel data source. The final presentation combines the two dates into one field in a format to display like this for example: 10:00A - 10:30A. Instead, it comes messed up with a combination of characters I can't reproduce in this message other than by providing a picture.





This is the code I have in the field ...

{MERGEFIELD r1_srl \@"h:mmA/P"} - {MERGEFIELD r1_rsu \@"h:mmA/P"}
Reply With Quote
  #2  
Old 04-22-2017, 12:39 PM
JennEx JennEx is offline Odd Result In Combined Merged Time Fields Windows XP Odd Result In Combined Merged Time Fields Office 2013
Competent Performer
Odd Result In Combined Merged Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

I suspect it doesn't like the format h:mmA/P (although is an ok custom format in Excel), as h:mm AM/PM provides a clean result.
However, this format is inappropriate for this report due to our standard. Is there anyway I can accomodate the preferred h:mmA/P format?
Reply With Quote
  #3  
Old 04-22-2017, 11:04 PM
macropod's Avatar
macropod macropod is offline Odd Result In Combined Merged Time Fields Windows 7 64bit Odd Result In Combined Merged 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

Quote:
Originally Posted by JennEx View Post
h:mm AM/PM provides a clean result.
However, this format is inappropriate for this report due to our standard.
The 'strange' 10:00 二十三/P output is directly attributable to your A/P switch. What is your 'standard'?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 04-22-2017, 11:08 PM
gmayor's Avatar
gmayor gmayor is offline Odd Result In Combined Merged Time Fields Windows 10 Odd Result In Combined Merged Time Fields Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You cannot use only part of a field nor make up your own switches, so with a bit of lateral thinking the following construction should work

{ SET A { MERGEFIELD r1_srl \@"AM/PM" } }{ SET B { MERGEFIELD r1_rsu \@"AM/PM" } }{ MERGEFIELD r1_srl \@"h:mm" }{ IF A = "AM" "A" "P" } - { MERGEFIELD r1_rsu \@"h:mm" }{ IF B = "AM" "A" "P" }


You might find it simpler to use http://www.gmayor.com/export_field.htm to convert the text string above to working fields.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 04-24-2017, 06:37 PM
JennEx JennEx is offline Odd Result In Combined Merged Time Fields Windows XP Odd Result In Combined Merged Time Fields Office 2013
Competent Performer
Odd Result In Combined Merged Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Thank you!

Thank you both for your support. It is very much appreciated when people unselfishly contribute their knowledge to help others.

GMayor ... your solution provided exactly what I was looking for.
Reply With Quote
  #6  
Old 04-27-2017, 07:57 PM
JennEx JennEx is offline Odd Result In Combined Merged Time Fields Windows XP Odd Result In Combined Merged Time Fields Office 2013
Competent Performer
Odd Result In Combined Merged Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default Adapting Mail Merge Field To Blank Out Empty Instances

Further to this request, based on this helpful solution, I am encountering an issue I hope can be easily overcome.

In my database, if a service is not required, the two times normally displayed are represented simply by hyphens.

In this example posted, there are no services scheduled for sections 2 - 4.

How can I eliminate that format and just leave those fields blank?


image album upload

The textual representation of the mergfield of the highlighted field is:

{ SET A { MERGEFIELD r2_srl \@"AM/PM" } }{ SET B { MERGEFIELD r2_sru \@"AM/PM" } }{ MERGEFIELD r2_srl \@"h:mm" }{ IF A = "AM" "A" "P" } - { MERGEFIELD r2_sru \@"h:mm" }{ IF B = "AM" "A" "P" }
Reply With Quote
  #7  
Old 04-27-2017, 08:12 PM
macropod's Avatar
macropod macropod is offline Odd Result In Combined Merged Time Fields Windows 7 64bit Odd Result In Combined Merged 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 r1_srl}= "" "" "{SET A {IF{MERGEFIELD r1_srl \@ "AM/PM"}= "AM" "A" "P"}}{SET B {IF{MERGEFIELD r1_sru \@ "AM/PM"}= "AM" "A" "P"}}{MERGEFIELD r1_srl \@ "h:mm"}{IF{REF A}={REF B} "" {REF A}}-{MERGEFIELD r1_sru \@ "h:mm"}{REF B}"}

Note: The '{IF{REF A}={REF B} "" {REF A}}' construction suppresses the first A/P if both times are AM or PM. If you don't want to do that, simply replace that part of the field code with '{REF A}'.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 04-28-2017, 09:05 AM
JennEx JennEx is offline Odd Result In Combined Merged Time Fields Windows XP Odd Result In Combined Merged Time Fields Office 2013
Competent Performer
Odd Result In Combined Merged Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Awesome job Paul. I like the format you put forth. But the blank services are showing now as ---P. Can this be easily resolved to either blank this out or even just ---?
Reply With Quote
  #9  
Old 04-28-2017, 03:14 PM
macropod's Avatar
macropod macropod is offline Odd Result In Combined Merged Time Fields Windows 7 64bit Odd Result In Combined Merged 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 r1_srl}= {MERGEFIELD r1_sru} "" "{SET A {IF{MERGEFIELD r1_srl \@ "AM/PM"}= "AM" "A" "P"}}{SET B {IF{MERGEFIELD r1_sru \@ "AM/PM"}= "AM" "A" "P"}}{MERGEFIELD r1_srl \@ "h:mm"}{IF{REF A}={REF B} "" {REF A}}-{MERGEFIELD r1_sru \@ "h:mm"}{REF B}"}
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 04-29-2017, 08:58 AM
JennEx JennEx is offline Odd Result In Combined Merged Time Fields Windows XP Odd Result In Combined Merged Time Fields Office 2013
Competent Performer
Odd Result In Combined Merged Time Fields
 
Join Date: May 2010
Posts: 162
JennEx is on a distinguished road
Default

Thank you Paul. This is proving to be working so far!
You help is once again very appreciated.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Odd Result In Combined Merged Time Fields Adding sequential Number in a document that already has merged fields inserted Highlander01 Mail Merge 3 01-19-2016 12:33 PM
Time allotted minus time used with result in hour and minute esther6086 Excel 2 04-29-2014 05:03 PM
Formatting Merged Time Values Between Excel & Word JennEx Mail Merge 1 07-12-2012 02:54 PM
combined in-box for webmail accounts sonoman Outlook 1 07-19-2011 02:50 PM
word and excel combined jasperman Word 1 08-23-2010 06:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:47 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