formatting mailmerge field

[Edoardo Benussi [MVP]]

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 ;-)

 

[Peter Jamieson]

Is that

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

?

Peter Jamieson

 

[Edoardo Benussi [MVP]]

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

 

[Peter Jamieson]

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

{ 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

 

[Edoardo Benussi [MVP]]

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 }" }" }

?

[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.

 

[Peter Jamieson]

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.

If you do something like

{ SET date2 "{ MERGEFIELD MYDATE }" }

then

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

what happens?

or even

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

?

[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.

 

[Edoardo Benussi [MVP]]

Peter Jamieson wrote:
[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.