I want "0's" to show where there is a null value

[MarieK]

I have created a pension query. I now want to create a report. In my query
are a series of years (2000, 2001, 2002...) some employeeswill not have any
hours for those years (hence it having a null value/ being blank) but it
looks funny int he report for there not to be anything.

SO, I want zeros to appear in the null value so on my report there are no
blanks. Can this be done? How? Is it something I do in the table? Or query
that was used to generate the report?

 

[stumac]

Hi, you can use the Nz function:

Amount1: NZ([Amount],0)

This will give you a field called amount1 and will display the data from the
amount field or a 0 if the field is null. This would be entered in the query
not the table.

Hth

Stu

 

[Jeff Boyce]

I suspect the Nz() expression you gave will give a "0" for both a Null value
and a value of "0".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi, you can use the Nz function:

Amount1: NZ([Amount],0)

This will give you a field called amount1 and will display the data from the
amount field or a 0 if the field is null. This would be entered in the query
not the table.

Hth

Stu

I have created a pension query. I now want to create a report. In my query
are a series of years (2000, 2001, 2002...) some employeeswill not have any
hours for those years (hence it having a null value/ being blank) but it
looks funny int he report for there not to be anything.

SO, I want zeros to appear in the null value so on my report there are no
blanks. Can this be done? How? Is it something I do in the table? Or query
that was used to generate the report?

 

[Jeff Boyce]

How will you be able to tell the difference between a Null value you are
showing as a "0", and an actual value of "0"?

(The Nz() function provided elsethread will convert nulls to zeros.)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[stumac]

This is true but this is what was asked for :

SO, I want zeros to appear in the null value so on my report there are no
blanks.

stu

 

[fredg]

I have created a pension query. I now want to create a report. In my query
are a series of years (2000, 2001, 2002...) some employeeswill not have any
hours for those years (hence it having a null value/ being blank) but it
looks funny int he report for there not to be anything.

SO, I want zeros to appear in the null value so on my report there are no
blanks. Can this be done? How? Is it something I do in the table? Or query
that was used to generate the report?

You can use the Format property of the control in the Report.
Set the Format property to:
#;-#;0;0

Better yet, to show that the value is not an actual 0, use:
#;-#;0;"N/A"

Look up Format Property + Number and Currency datatypes in Access
help.