#1
|
|||
|
|||
Unable To Get Proper Merged Results
I have a merged report that relies on an Excel worksheet for its data.
In most part, the mail merge document merges the data correctly. I have 8 tables that share the same structure as far as the 5 mail merge fields go found in each table. The fields are all calculated the same, with only their mail merge field names differing from one table to another. Despite copying the mail merge formula to each respective field between the 8 different tables, and changing the respective mail merge field names, I am getting inconsistent results on a few. The data is consistent, and all the header rows match the merge fields as far as I can tell. I have attached the mail merge document (DT15v1_5.docx), the data source (Workbook "Aug-30 (Sun) schedule_3.xlsx" Worksheet "CORE") and a reference document showing all the 8 sections and their mail merge fields so that the formula can be easily read and modified. In both the mail merge document and the reference document, the "code" in red is what is being misrepresented in the mail merge document. The fields in black are all correct. I am wondering if someone would be so kind as to take a look at these problems and try to isolate their cause? I've copied, and reedited each several times and only ever get the wrong presentation. If I can offer more information to assist, please ask. Thank you all in advance. |
#2
|
||||
|
||||
Hi Jenn,
The problem with a number of your fields is that there is no space after the '=' sign. While it's possible to get away without a space before the '=' sign when there's a field brace preceding it, you need it to separate the sign from the text you're testing against. FWIW, you'd have had none of this trouble if you'd simply copy & pasted the field codes I gave you, then merely changed the mergefield names. It's not apparent to me the checkbox fields have an error. See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul,
Happy to see your help! While I don't doubt your advice, what I fail to understand is why my formula works in one field, I copy it to another, change the field names accordingly and get different results? ie ... look at the two formula in TS2 and TS4. Other than the r2 / r4 they are identical. Neither of them have the spaces you had suggested. I've tried rewriting the formula by inserting spaces after the "=" and don't see a change. TS2 TS4 There is one field that was inserted using Word's "Insert Mail Merge Field". Can't really screw that up, yet it's giving me a "0" instead of WPE (as per the datasource). Didn't require any editing on my part. All the others work having used the same method. Quote:
I've applied the data source and SQL to your report and regrettably I see no difference. Here is what I'm seeing. (this is your report) The red is still indicating where the wrong information is presented. This shows in both the preview and final document. The two red checkboxes should have the X in them. The rule is if = "REL" then box is of the checked type. Both fields in the datasource = "REL" Confused. I'm not sure I'm grasping what you're sharing. |
#4
|
||||
|
||||
There is only one record that conforms to those specs - on row 33. The attached shows what I get for that record with the document attached to my last post.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Yes ... only one record.
Can there any explanation why you are getting the proper results and I'm getting messed up mixed results? I swear my results are as I present them ... the last thing I want to do is waste both our time. The image you attached ... is it from the same merge document you attached in your previous post with no edits since? As we both have the same data, and we're both using your report, one would think the results would be the same? |
#6
|
||||
|
||||
The attachment is the actual merged output from the record concerned. Your image is what I'd expect to see from your version of the mailmerge main document.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Quote:
Would you be able to attach the mail merge document you got these results with? |
#8
|
||||
|
||||
The mailmerge main document I got the results with is already attached to post #2, using your data file from post #1.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
OK ...
I'll download again and apply my data to it and see what I get. I've done that already though and got the results I posted in #3. This is why I'm so confused. I don't know why we're getting different results when using the same resources. |
#10
|
|||
|
|||
Using the report you provided in #2 (reattached), with no changes ... simply downloaded, put in my folder, opened and data applied with SQL, I've attached the final document.
Some corrections, but some new and pre-existing inconsistencies. (highlighted in yellow. In a totally separate process, I copied the fields that worked, pasted them in the cells where they didn't, changed the field names and still received the same inaccuracies. |
#11
|
|||
|
|||
This is ALT-F9 view of your report.
The field on the left in red produces the correct results. The one on the right not. I do not see any differences between the two except for the field references (r3 vs. r4) |
#12
|
||||
|
||||
I can only conclude that differences between our systems mean they handle your workbook data slightly differently. Some of that may be related to your use of custom time formats that in cells that don't contain times. How about we start with the workbook and change all the formatting to 'General', then apply the data & time formats to the entire columns containing those data (dates - B, AP, AS, AV; times - N, O, Z, AB, DZ, EA, EC, ED). That way at least we might start talking on the same wavelength. See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
I downloaded your data source file, and placed it in a directory dedicated to your contributions to this project. I'm confirming that all times and dates are in the proper format (as your instructions eluded), with the exception of the r1 through r_8 srl and sru times which are decimal. This is the format of the report you sent
I then re-downloaded your merge document (report) from post #2, and put it in the same directory. I opened up the report with Word, selected your data file and worksheet "CORE" as its recipient (source). From 'Edit Recipients' I selected "DT" from the type field. 'Preview Results' : (attachment) Still the same Select 'Finish & Merge' > 'Edit Individual documents' ... Get a "Field calculation error occured in record 1", and saved the resulting document. I've attached the completed document. As an aside ... when I went to save the document, it insisted on giving it the name of 1.docx, while the document title was letters2. Is this normal? I would have thought it would want to save it as the same name as the document title. I don't know Paul. Gremlins? |
#14
|
||||
|
||||
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
Where did you put the new version (data file or report file) ...?
And so we're on the same page, (I'm guessing you have reason behind your approach), but I don't seem to be having issues with the time fields. It's the others line: r#_relchg, r#_crew, r#_division, r#_base and r#_pitch that are being represented improperly in the report. For example, the boxes under "CHANGE" shouldn't have any time values in them which a couple do (They are supposed be be simply "r#_division r#_base/r#_pitch ... Bantam 82'/55', not ). When I look at both your formula and mine, I don't even see any reference to time fields, nor is the data for those respective fields time. I wonder ... the text values in the data for r#_base and r#_pitch include an apostrophe. (82', 55'). Might this be causing issues? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
proper | snoforlife | Excel Programming | 0 | 01-26-2016 02:16 PM |
Hyperlinks Not Linking to Proper Sections of Doc | MoiraB | Word | 2 | 08-26-2015 07:13 PM |
Table will not allow sorting because "cells are merged". I can't find the merged cells. | wendyloooo | Word Tables | 1 | 05-26-2015 01:19 PM |
Proper Text Format | sufian,naeem | Excel | 1 | 05-05-2014 05:59 AM |
2013 search results take a long time - they fill in as results in reverse date order | themookman | Outlook | 0 | 10-11-2013 12:01 PM |