Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-09-2013, 07:21 AM
Abacus1234 Abacus1234 is offline Yes/No field does not work in mailmerge Windows Vista Yes/No field does not work in mailmerge Office 2010 32bit
Novice
Yes/No field does not work in mailmerge
 
Join Date: Nov 2011
Posts: 24
Abacus1234 is on a distinguished road
Default Yes/No field does not work in mailmerge

In the database, I have a yes/no field as a checked box. In a Word document I use an If statement referencing -1. This does not work in 2013. Does work in all prior offices.
Reply With Quote
  #2  
Old 10-11-2013, 12:28 AM
macropod's Avatar
macropod macropod is offline Yes/No field does not work in mailmerge Windows 7 32bit Yes/No field does not work in mailmerge 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

Have you checked what the field actually returns in Office 2013 (i.e. without the IF test)?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-11-2013, 06:43 AM
Abacus1234 Abacus1234 is offline Yes/No field does not work in mailmerge Windows Vista Yes/No field does not work in mailmerge Office 2010 32bit
Novice
Yes/No field does not work in mailmerge
 
Join Date: Nov 2011
Posts: 24
Abacus1234 is on a distinguished road
Default Yes/No field

Hi Paul,

I found that taking of the negative made it work, so instead of -1, I am using 1. Thanks for your response.

Brenda
Reply With Quote
  #4  
Old 10-11-2013, 07:33 PM
macropod's Avatar
macropod macropod is offline Yes/No field does not work in mailmerge Windows 7 32bit Yes/No field does not work in mailmerge 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 that your fields will work with any version, I'd suggest changing the test so that it evaluates whether the checkbox state is 'False' (i.e. 0). For example:
{IF{MERGEFIELD checkbox} <> 0 "Yes" "No"}
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 10-12-2013, 07:03 AM
Abacus1234 Abacus1234 is offline Yes/No field does not work in mailmerge Windows Vista Yes/No field does not work in mailmerge Office 2010 32bit
Novice
Yes/No field does not work in mailmerge
 
Join Date: Nov 2011
Posts: 24
Abacus1234 is on a distinguished road
Default making it work on all versions

Hi Paul,
Thanks for the advise.

On the same subject, I have another complicated IF statement used in hundreds of documents which I have had to change to accommodate Office 2013.

When using If ....<> "" , to make it work in 2013, I had to put a space between the quotes ....<> " "

The 2013 version doesn't work on previous versions, and what I was using in previous versions doesn't work in 2013.

It means maintaining 2 sets of documents for the same application, which is the kiss of death as far as I am concerned.

I don't suppose there is a solution, but thought I would mention it.

Any signs that Microsoft might return to or fix the use of dde?

Brenda
Reply With Quote
  #6  
Old 10-12-2013, 07:36 PM
macropod's Avatar
macropod macropod is offline Yes/No field does not work in mailmerge Windows 7 32bit Yes/No field does not work in mailmerge 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

Hi Brenda,

If you need the mergefields to work with both Office 2013 and earlier versions, and if the fields being evaluated are supposed to be numeric or Boolean, you could use something like:
{IF{={MERGEFIELD checkbox} \# 0} <> 0 "Yes" "No"}
Such a construction will force all the fields to be evaluated as numbers, including those that are empty or output a space when they should be empty.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 10-13-2013, 07:45 AM
Abacus1234 Abacus1234 is offline Yes/No field does not work in mailmerge Windows Vista Yes/No field does not work in mailmerge Office 2010 32bit
Novice
Yes/No field does not work in mailmerge
 
Join Date: Nov 2011
Posts: 24
Abacus1234 is on a distinguished road
Default Differences between 2013 and previous versions

Hi Paul,

Thanks for the info on Yes/no, it does work.

I have another complicated IF statement used in hundreds of documents which I have had to change to accommodate Office 2013.(which I have attached in a word document)

When using If ....<> "" , to make it work in 2013, I had to put a space between the quotes ....<> " "

The 2013 version doesn't work on previous versions, and what I was using in previous versions doesn't work in 2013.

It means maintaining 2 sets of documents for the same application, which is the kiss of death as far as I am concerned.

I don't suppose there is a solution, but thought I would mention it.

Any signs that Microsoft might return to or fix the use of dde?
Attached Files
File Type: docx That certain mortgage from James Trust Corp.docx (13.1 KB, 10 views)
Reply With Quote
  #8  
Old 10-13-2013, 02:34 PM
macropod's Avatar
macropod macropod is offline Yes/No field does not work in mailmerge Windows 7 32bit Yes/No field does not work in mailmerge 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

Hi Brenda,

Instead of:
{IF{MERGEFIELD Buyer2Name}<> " " {IF{MERGEFIELD Buyer3Name}<> " " ", " " and " } ""}
you might be able to use field coding like:
{IF{MERGEFIELD Buyer2Name }= "??*" {IF{MERGEFIELD Buyer3Name}= "??*" ", " " and "}}
Basically, the above field code tests whether the mergefields contain at least two characters (on the assumption you don't have any one-character buyer names).

I doubt MS has any plans to update DDE and, even if they did, there's no reason to suppose it would fix this problem.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 10-13-2013, 04:15 PM
Abacus1234 Abacus1234 is offline Yes/No field does not work in mailmerge Windows Vista Yes/No field does not work in mailmerge Office 2010 32bit
Novice
Yes/No field does not work in mailmerge
 
Join Date: Nov 2011
Posts: 24
Abacus1234 is on a distinguished road
Default Differences between 2013 and previous versions

Thanks for the suggestion, I will try it. It would be extremely helpful to be able to maintain only one set of documents.

It is because dde does not work that I have this problem.

In 2013 dde does not work, during mail merge it closes the Access application after one document has been printed making it impossible to use in my application. I had to change the mail merge data transfer method and dump the contents of the query into a text file. The text file does not carry formatting for date/time, currency or support this If statement. DDe carried all the formatting from the Query, it being tailor made for use with Access.

So I have had to change the formatting in hundreds of documents, and so end up maintaining 2 sets of documents. All prior versions of Office before 2013 allowed the use of dde, 2013 does not.

I find your suggestions very helpful in finding alternative syntax for these problems. Thanks.
Reply With Quote
  #10  
Old 10-13-2013, 04:48 PM
macropod's Avatar
macropod macropod is offline Yes/No field does not work in mailmerge Windows 7 32bit Yes/No field does not work in mailmerge 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 Abacus1234 View Post
I had to change the mail merge data transfer method and dump the contents of the query into a text file. The text file does not carry formatting for date/time, currency or support this If statement.
Text & currency formatting can be controlled from within Word, via the use of formatting switches in the fields.

Mailmerge Number & Currency Formatting
To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
• select the field;
• press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
• edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Note1: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
• \# 0 for rounded whole numbers
• \# ,0 for rounded whole numbers with a thousands separator
• \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
• \# $,0 for rounded whole dollars with a thousands separator
• \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
Note2: If you use a decimal tab or right-aligned tab to align the values and insert a tab into the field code after the $ sign, you can have the values aligned accordingly.

Mailmerge Date Formatting
To get the date format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy".
Note: You can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.

PS: The data export to the text file may be the cause of the unwanted spaces referred to in your previous posts.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 10-14-2013, 06:37 AM
Abacus1234 Abacus1234 is offline Yes/No field does not work in mailmerge Windows Vista Yes/No field does not work in mailmerge Office 2010 32bit
Novice
Yes/No field does not work in mailmerge
 
Join Date: Nov 2011
Posts: 24
Abacus1234 is on a distinguished road
Default formating in word mergefields

Hi Paul,

Thanks for your help. You have helped me solve two of the more obscure formatting issues.

I appreciate the input.
Reply With Quote
Reply

Tags
mailmerge, yes/no

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge Word documents using a mailmerge field Concertina Mail Merge 3 02-18-2013 04:12 AM
Yes/No field does not work in mailmerge Mailmerge to Email with a mailmerge attachment Baldeagle Mail Merge 8 02-11-2013 04:40 AM
Yes/No field does not work in mailmerge Mailmerge to Email with a mailmerge attachment Baldeagle Mail Merge 13 05-29-2012 02:04 PM
Formula to subtract one month from due date field in reminder field ghumdinger Outlook 1 10-01-2011 12:09 AM
Yes/No field does not work in mailmerge Making the IF field work (not Mail Merge) Caroline Mail Merge 5 04-11-2011 04:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:55 AM.


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