Formatting Complex Mail Merge Field

[Jenn68]

I have this mail merge field:

{ IF { MERGEFIELD AltStaffStart } = "X" "" {MERGEFIELD "AltStaffStart" \@ "h:mm am/pm" }}

If field AltStaffStart equals "X", then display the field as blank, otherwise, display the value of AltStaffStart in time format (h:mm am/pm).

All I am getting is the decimal value of AltStaffStart.

What do I need to do to have the contents displayed properly?

[macropod]

Hi Jenn,

Your field suggests you have mixed data in the AltStaffStart column. Not good. As far as the mailmerge is concerned, Word considers the column to now contain text. You can, however, redeem the situation for the second half of your expression with:
{IF{MERGEFIELD AltStaffStart}<> "X" {QUOTE{SET ss{={MERGEFIELD AltStaffStart}*86400 \# 0}}{SET hr{=INT(ss/3600)}}{SET mn{=INT((ss-hr*3600)/60)}}{SET sec{=MOD(ss,60) \# 0}}"{hr}:{mn}:{sec}" \@ "HH:mm:ss am/pm"}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

[Jenn68]

Hi Paul,

Thank you so much for your reply. If it took you as long to figure that complex field out as long as it did for me to type it in ... let me express how much I appreciate the effort! Wow!

Despite my entering as you had instructed (and I am not one to admit never making errors), the resulting value (when the excel data = 7:00 AM) turns out as 00:00:00 AM. I'm uncertain if it's my data, or a typo in my field. I'm having difficulty trying to edit the field ... I right click the field to choose "Toggle Field Code", but I can't seem to continue to toggle deeper into the 00:00:00AM portion. I get {IF7:00 AM<>""X"00:00:00 AM }, and toggle deeper to {IF {MERGEFIELD AltSTaffStart}<>"X"00:00:00 AM }, but that's it.

If the explanation for the improper result is from a typo in the field, how do I expose it to edit it?

Jenn

[macropod]

hi Jenn,

With the field code, all of the spaces are crucial. Omit one and it won't work. Having extras generally won't have an adverse effect, though. To edit the field code, select the whole field and press Shift-F9. Alternatively, Alt-F9 toggles the display for all fields. Here's some links to a couple of macros you can download for converting field codes:
To Text - http://www.gmayor.com/export_field.htm#FieldToText
From Text: http://www.gmayor.com/export_field.htm#TextToField
Simply copy & paste the 'field' from my post into your document, select it, then run the macro from the second link.

If it's still not working, post back a couple of examples showing what you're getting in the document as decimals for the corresponding times in Excel.

[Jenn68]

I haven't had an opportunity to review the field code as you had suggested Paul, but will shortly.
On a similar note dealing with times not displaying properly, but not based on the same complex type field, I've stumbed onto an observation. In another field in which I display time value {MERGEFIELD"Lights_ON" \@ "h:mm am/pm"} I also get a decimal value displayed instead of a formatted time. I've made an unusual observation. My data source has the "Lights_ON" data formatted as a time value "8:22:00 PM". However, when I view this same column of data in the "select recipients" dialogue window of mail merge, it is displayed as a decimal. (???)

Similarly, the data in my source with a text vale of "<=8:22 pm" is portrayed in the select recipient dialogue as 12:00:00 AM. I use time quite extensively in my mail merge table, but most, do noy have these kind of problems. I'm not sure what I am doing wrong that the data is being seen by Word in a different format than the data source.

[macropod]

Hi Jenn,

What data connection method is your mailmerge using? You may be able to circumvent all these issues by switching to DDE.

[Jenn68]

Not likely DDE
I read a bit about it, as I know I am going to need it eventually. Right now, I'm using Excel 2003, but know that within a few weeks, will be required to transition to Excel 2007. I think with 2007 I will have to use DDE, as my experiments with it so far show no love for any time values in my documents.

I'm trying to automate my reporting from Excel, with no user involvement from within Word (if that's possible). - - Click a button in Excel, print a Word mail merge document --
My (mis) understanding is that DDE has to be assigned to each report prior to printing? Or can the setting be saved as part of the report? I dunno.

You're more than welcome to continue the great job helping me Paul -wink wink-. Just kidding, I don't want to abuse your good will.

Jenn

[jeffreybrown]

Hi Jenn,

Paul is definetly the expert here, but I ran across a post by him (not sure where it is now), but maybe he is referring to this on the DDE...

http://support.microsoft.com/kb/320473

[macropod]

Hi jenn,

DDE is just one of the data connection methods available for a mailmerge. Once set, you can forget it, just like any of the other connection methods.

Jeff's link points you in the right direction.

[Jenn68]

I'm still plugging away to get the field code to work in my document. Discovering that it is far too easy to make an error entering the field code Paul suggested manually, I am taking the advice to use the macro.

I have copied the field code text from this thread, and pasted it in the cell of my main document. I highlight the pasted text, and pres the play button in VBA editor to execute the macro referred to in Post #4 of thi thread.

I am getting an error with line:

Please Login or Register  to view this content.

"The range cannot be deleted."

Thoughts?

[macropod]

Hi jenn,

I can't reproduce that.

[Jenn68]

NP Paul, I'll keep plugging away, either by manual input, or by trying to get the macro to work. I've tried several other times, but no go. I'm wondering if it's because it's in the footer or what.

I've been playing with the DDE, and it looks like it has potential. Sadly, it doesn't allow me to select the individual worksheet in the data workbook for the data source. Simply moving the worksheet to the first position doesn't seem to work. I suppose I could identify the define the entire worksheet as a named range?

[macropod]

Hi jenn,

Yes, it may indeed be because you're not working in the body of the document. The macro wasn't designed with the thought of it being used anywhere else - you can always do the conversion in the body of the document, then cut & paste the code to wherever else you want to use it.

As for DDE, it's a while since I've used it but I thought it either defaulted to the first worksheet or allowed you to nominate one.