formatting mailmerge field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Edoardo Benussi [MVP] - 20 Apr 2005 10:15 GMT

Hi all,

I have a word document with mailmerge.
The data source is a msquery through system dsn (odbc)
and data are stored in sql server.

The problem is date format:

with
{MAILMERGE MYDATE}
i see
31/12/2004 0.00.00

with
{MAILMERGE MYDATE \@ "dd/MM/yyyy" }
i see
20/04/2005
that is today.

There is a solution ?
TIA

sorry for my poor english ;-)

Signature

Edoardo Benussi - edo@mvps.org
Microsoft® MVP - Windows Server Networking
http://mvp.support.microsoft.com

Reply to this Message

Peter Jamieson - 20 Apr 2005 10:35 GMT

Is that

{ MERGEFIELD MYDATE \@ "dd/MM/yyyy" }

?

Peter Jamieson

> Hi all,
>
[quoted text clipped - 19 lines]
>
> sorry for my poor english ;-)

Reply to this Message

Edoardo Benussi [MVP] - 20 Apr 2005 10:51 GMT

> Is that
>
> { MERGEFIELD MYDATE \@ "dd/MM/yyyy" }
>
> ?

[cut]

Sorry Peter,
I have made an error in writing.
you have to read "MERGEFIELD" instead of "MAILMERGE"
in my prevous post.
tnx

Signature

Edoardo Benussi - edo@mvps.org
Microsoft® MVP - Windows Server Networking
http://mvp.support.microsoft.com

Reply to this Message

Peter Jamieson - 20 Apr 2005 19:29 GMT

I have tried to replicate this problem using an SQL server source but have
not been able to do so.

If you do something like

{ SET date2 "{ MERGEFIELD MYDATE }" }

then

{ REF date2 \@ "dd/MM/yyyy" }

what happens?

or even

{ SET date2 "{ QUOTE { MERGEFIELD MYDATE }" }" }

?

Peter Jamieson

>> Is that
>>
[quoted text clipped - 8 lines]
> in my prevous post.
> tnx

Reply to this Message

Edoardo Benussi [MVP] - 21 Apr 2005 09:30 GMT

> I have tried to replicate this problem using an SQL server source but
> have not been able to do so.
[quoted text clipped - 14 lines]
>
> ?

[cut]

Thanks Peter,
now I try.

I have made more tests and
seems to me that the problem is as msquery read an datetime field
in sql server.

I'll write the solution if there is one.

Regards.

Signature

Edoardo Benussi - edo@mvps.org
Microsoft® MVP - Windows Server
http://mvp.support.microsoft.com

Reply to this Message

Peter Jamieson - 21 Apr 2005 10:16 GMT

OK, some more info that may help you:
a. when you use MS Query to set up a merge data source, it is really only
used to generate the SQL Query that Word uses to get the data. After you
have set up the merge, Word does not use MS Query to get the data (unless
you are connecting to a .dqy that you have saved from MS Query, in which
case things are different).
b. you can inspect the value of
ActiveDocument.MailMerge.DataSource.QueryString to see what SQL Word is
issuing. However, in Word 2002 you may not be able to do that - in that case
you can save your document as HTML format, open the HTML file using e.g.
Notepad, and look for the block of MailMerge information near the top.
c. (Probably not relevant) By the time the field format switches operate on
the data, all the data is in /text/ format (in other words, as far as I
know, the switches are not working with the underlying ISO datatime format,
they are working with the text string you see when you just use { MERGEFIELD
MYDATE }
d. If you are using either Word 2002/2003 yu can always try the OLEDB route
instead...

Peter Jamieson

>> I have tried to replicate this problem using an SQL server source but
>> have not been able to do so.
[quoted text clipped - 26 lines]
>
> Regards.

Reply to this Message

Edoardo Benussi [MVP] - 21 Apr 2005 10:28 GMT

[cut]

> c. (Probably not relevant) By the time the field format switches
> operate on the data, all the data is in /text/ format (in other
> words, as far as I know, the switches are not working with the
> underlying ISO datatime format, they are working with the text string
> you see when you just use { MERGEFIELD MYDATE }

[cut]

goal!
i have modified the "select" instruction in msquery
from
select mydate from ...
in
select cast(mydate as char(20)) from ...

and
{ MERGEFIELD MYDATE \@ "dd/MM/yyyy"}
work fine.
tnx.

Bye.

Signature

Edoardo Benussi - edo@mvps.org
Microsoft® MVP - Windows Server
http://mvp.support.microsoft.com

Reply to this Message