Merging query with Nz to Word

[Jordan]

I have a query that does calculations and has several
fields that contain the Nz() function. I need it for the
obvious reason that some of the fields the calculations
are based on may contain null values, and a null result
does nothing for me. However, when I try to link my
documents to this database using OLE DB, this particular
query won't come up on the Table/Query List, and I've
figured out that it's because of the Nz() function. Does
Word not like Nz? Is there any other way I can
accomplish the task of getting non-null values in my
calculations but still link to Word without using a DDE
link?

 

[Brian Camire]

Try using

IIf(IsNull([Your Field]),[Value If Null],[Your Field])

instead of

Nz([Your Numeric Field],[Value If Null])

Unlike IIf, Nz is an Access-specific function.

 

[Jordan]

Duh! Thanks for that one, even though I should've
figured it out. Does the fact that Nz is Access specific
mean that it cannot be used by other programs?

-----Original Message-----
Try using

IIf(IsNull([Your Field]),[Value If Null],[Your Field])

instead of

Nz([Your Numeric Field],[Value If Null])

Unlike IIf, Nz is an Access-specific function.

I have a query that does calculations and has several
fields that contain the Nz() function. I need it for the
obvious reason that some of the fields the calculations
are based on may contain null values, and a null result
does nothing for me. However, when I try to link my
documents to this database using OLE DB, this particular
query won't come up on the Table/Query List, and I've
figured out that it's because of the Nz() function. Does
Word not like Nz? Is there any other way I can
accomplish the task of getting non-null values in my
calculations but still link to Word without using a DDE
link?

.

 

[Brian Camire]

The short answer is that it cannot be used directly by other programs.

The longer answer goes something like this...

The Nz function is actually a method of the Access "Application" object. If
you really wanted to, you could create an instance of an Access
"Application" object from another application and call the Nz function. For
example, executing the following lines from the Immediate pane of the Visual
Basic Editor window in Word:

Set objApplication = CreateObject("Access.Application")
Debug.Print objApplication.Nz(Null, 1)

will print a "1" in the Immediate pane.

However, this is a very expensive way of doing something that can be done in
a lot more simply by using IIf and IsNull (and, unlike IIf and IsNull, will
not work in a query either).

Duh! Thanks for that one, even though I should've
figured it out. Does the fact that Nz is Access specific
mean that it cannot be used by other programs?

-----Original Message-----
Try using

IIf(IsNull([Your Field]),[Value If Null],[Your Field])

instead of

Nz([Your Numeric Field],[Value If Null])

Unlike IIf, Nz is an Access-specific function.

I have a query that does calculations and has several
fields that contain the Nz() function. I need it for the
obvious reason that some of the fields the calculations
are based on may contain null values, and a null result
does nothing for me. However, when I try to link my
documents to this database using OLE DB, this particular
query won't come up on the Table/Query List, and I've
figured out that it's because of the Nz() function. Does
Word not like Nz? Is there any other way I can
accomplish the task of getting non-null values in my
calculations but still link to Word without using a DDE
link?

.

 

[Albert D. Kallal]

You could also consider using my solution.

It works regardless if your query has functions in it.

Try downloading my mail merge....as it will work even if you use functions..

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html