How do I output zeros in access for null values

[Guest]

How do I output zeros in access for null values

 

[JK]

Use the Nz() fonction
=Nz(YourVar,0)

It is the same as:
=IIf(IsNull(YourVar),0,YourVar)

Regards/JK

 

[Guest]

JK,

Thanks for the reply, I tried the Nz function but maybe I'm mixed up. Here
is the senario. I create macros in access that I drag on my desktop to grab
data from an ODBC that outputs a sort iand opens in an excel spreadsheet.
There are a number of varying projects and I need to get the number of
completes for each project, the number in process and the number pending.
This all works fine but the number of projects complete is different than the
number in-process and the number of projects pending. I'm looking for a
function that if one of these project fields is Null, then output a zero.
Everytime I run this I have to cut and paste so I can keep them lined up. For
example, my output may be
Complete In-process Pending
1 2 3
2 5 1
3
4
5

It needs to output
Complete In-process Pending
1 0 1
2 2 0
3 0 3
4 0 0
5 5 0


Any ideas?

 

[Lynn Trapp]

If you run a query in Access it could look like this:

SELECT Complete, NZ([In-Process],0), AS InProcess, NZ([Pending],0) AS Pend
FROM YourTable;

 

[Guest]

I've tried this every which way but sideways. The best I can do is get an
error that the data field is a mismatch. I'm trying to get zeros put in null
fields that are date fields. If a date exists in a completed field then the
item is completed. If a date exists in a received field but does not exist in
a started date field it is pending. If there is a date in the started date
field and not the completed date field, it is In-Process. I have to output
all the projects on a report so it reads all the program names and how many
of each is complete, pending and in-process. Right now I am doing get each
separately, and when I do, if there are no pendings for example, the program
name does not output so I run three queries and copy and paste the program
names and then add the zeros manualy as it all has to go on a chart. I know
there must be an easier way to do this. I have macros do most of it except
when it gets to a spread sheet, then I do the copy paste. I need it all in
one operation so others can run my reports easily when I'm gone.

 

[Bill Mosca, MS Access MVP]

Nz() is not going to do you any good for a date field. Maybe if you post the
query that is not doing what you want, someone can post a solution. It might
be something as simple as using an outer join or a union query.