How do I omit an empty date merge field?

[Guest]

I am getting garbage when a date merge field is empty in word 2002. I have
tried:

{ IF { MERGEFIELD "sdate" } = "" "" "{ MERGEFIELD "sdate" \@"MM/dd/yyyy" }"
} Which indicates, if the merge date field is empty, then display nothing,
else display the value in the merge date field. Yet, it displays,
06/09/2005. I checked the database and their is no value in the field.

The database is SQL Server 2000 and I am using Access 2002 to interface with
SQL Server. Then I am using Word 2002 to interface with Access. When I run
the query that Word is using to perform the merge, the date field displays
the data correctly. I am not having any problems with any other field, only
the date field.

 

[Graham Mayor]

You need to trap whatever the field *actually* produces, rather than what it
is supposed to produce.
Enter the following {mergefield sdate \@ "yyyyMMdd" }
Trap the resulting figure eg
{ IF { MERGEFIELD sdate \@ "yyyyMMdd" } <> "20050609" "{ MERGEFIELD sdate
\@"MM/dd/yyyy" }" }

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guest]

Thanks for your quick response.

I can't test against the 20050609 date, because that may be a legitamite
date. I am trying to test against an empty field. So if the date merge
field is empty, then don't display anything, else display the value stored in
the date field.

Since some of these records do not have date values in them, there should
not be any value appearing (ie 06/09/2005). Also, if I don't include the
date picture (\@), Word displays 12:00:00 AM for a date merge field that
should be empty.

If the field is empty then why is Word displaying something that is not there?

-Phil-

 

[Graham Mayor]

This is all to do with how applications store nul data. Word repoduces the
raw data rather than the formatted content of the data source. An empty date
field may not in fact be empty. You have to test for what is actually
produced. I included that date because you quoted it, I would think the
actual content is something different.

Try the following
{ IF { MERGEFIELD sdate } <> "12:00:00 AM" "{ MERGEFIELD sdate \@
"MM/dd/yyyy" }" }

Alternatively see the Excel Data section of
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guest]

Graham,

Thanks again for your quick response.

I understand what you are saying, the IF..ELSE..THEN has to have a value to
compare with in order for the statement to be true or false, depending on the
needs of the outcome. Can I test against "NULL"? If yes, what would the
syntax be? I ask because the date field in the database accepts NULL values
if no data was inserted. Therefore, if the database contains a NULL value, I
would like to test for that.

I have tried the following, but it does not work.

{ IF { MERGEFIELD sdate } = "NULL" "" "{ MERGEFIELD sdate \@

"MM/dd/yyyy" }" }

But, I think, for the moment, your work-around will probably be the best way.

-Phil-

 

[Guest]

Graham,

Thanks again for your quick response.

I understand what you are saying, the IF..ELSE..THEN has to have a value to
compare with in order for the statement to be true or false, depending on the
needs of the outcome. Can I test against "NULL"? If yes, what would the
syntax be? I ask because the date field in the database accepts NULL values
if no data was inserted. Therefore, if the database contains a NULL value, I
would like to test for that.

I have tried the following, but it does not work.

{ IF { MERGEFIELD sdate } = "NULL" "" "{ MERGEFIELD sdate \@

"MM/dd/yyyy" }" }

But, I think, for the moment, your work-around will probably be the best way.

-Phil-