if statements with dates in mailmerge

[walkerh]

This has me puzzled. We have a mailmerge document populated from an external database via a header and text file. In the letter we compare a Start Date with an End Date. If they are the same the letter only prints the Start Date m/d/y. If the End Date is greater than the Start Date, we format the Start Date for month/day add a dash and format the End Date month/day/year. We just ran into a case where if the month and day are the same in the Start Date and the same in the End Date the test fails and only the Start Date is printed. An example would be SD = 04/04/2007 and ED = 06/06/2007. changing the SD to 03/03/2007 or 05/05/2007 or the ED in similiar fashion yields the same failure. A difference in the year results in the desired behavior. Any thoughts?

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Peter Jamieson]

What fields are you using to do the comparison?

If you are doing something like

{ IF { REF EndDate } = { REF StartDate } "equal" "different" }

you need to do something more like

{ IF "{ REF EndDate }" = "{ REF StartDate }" "equal" "different" }

(I don't know why Word needs the quotes in this case) and in any case you
really need to format the dates as YYYYMMDD before you compare them (which
you may be doing) but you can try

{ ={ REF EndDate \@YYYYMMDD }-{REF StartDate \@YYYYMMDD }
\#"'{ REF StartDate \@"M/D" }-{ REF EndDate \@"M/D/YYYY" }';'x';'{ REF
StartDate \@"M/D/YYYY" }'" }

and put something instead of "x" if you need to cover the case where the
start date is after the end date.

If you're using MERGEFIELD fields, some of the above may not apply but you
still need the YYYYMMDD stuff.

Peter Jamieson

 

[Jules]

What fields are you using to do the comparison?

If you are doing something like

{ IF { REF EndDate } = { REF StartDate } "equal" "different" }

you need to do something more like

{ IF "{ REF EndDate }" = "{ REF StartDate }" "equal" "different" }

(I don't know whyWordneeds the quotes in this case) and in any case you
really need to format the dates as YYYYMMDD before you compare them (which
you may be doing) but you can try

{ ={ REF EndDate \@YYYYMMDD }-{REF StartDate \@YYYYMMDD }
\#"'{ REF StartDate \@"M/D" }-{ REF EndDate \@"M/D/YYYY" }';'x';'{ REF
StartDate \@"M/D/YYYY" }'" }

and put something instead of "x" if you need to cover the case where the
start date is after the end date.

If you're using MERGEFIELD fields, some of the above may not apply but you
still need the YYYYMMDD stuff.

Peter Jamieson






- Tekst uit oorspronkelijk bericht weergeven -

it could help you a lot by using a query containing this sort of
inteligence. I think the formula mentioned will work (except the
quotes around a field, I shouldn't use those). But a query is much
more easy to design.
If your external database doesn't allow to make queries, you could
export the data to a Excel or beter Access file.
Good luck

 

[Walkerh]

Thank you Peter. That seems to have cleared it up. I think I read somewhere that mail merge treats dates as text so telling it that these are dates makes sense in my ignorant mind. The 03/03/07 or 04/04/07 or 05/05/07 for a start date failing the <> in the IF when compared to 06/06/07 can be now be something to ponder if/when I ever get so leisure time.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com