Question Replace null value with zero in MSAcces 2 (1 Viewer)

[amunafc]

Replace Text with actual totals in MSAcces

Hi Guys

I have re-uploaded the database with few field names. Please download, unzip and open it. then run the report and export it to an excel or analyze it with excel. You will notice that the totals in the spreadsheet are not showing. it is only showing the following. Is there any way in MS Access of showing the totals rather than the text?

Text120: Text121:Text130:Text145:Text146:Text155:Text158:Text159:Text168:

 

[John Big Booty]

Have a look at the NZ() Function this should get you moving forward.

 

[amunafc]

@John Big Booty
Please go through the database and do it then send it back. have you seen the database itself? Its funny and you will admit.

 

[John Big Booty]

No I've not looked at the DB, but have you had a look at the NZ() function, this should do what you want, it will look something like;

Nz (varName, 0)

 

[amunafc]

@John Big Booty
I dont know where to put the NZ function am mad guys..

 

[John Big Booty]

As an example in Control Source for Text120 put;

=nz(Sum([2005/2006 Revised]),0)

In this case if Sum([2005/2006 Revised]) returns a Null value then 0 will be displayed otherwise the result of Sum([2005/2006 Revised]) will be displayed.

 

[amunafc]

Let me try and thanx for your time

 

[Poppa Smurf]

In your query I will use your field 2006/2007 Revised:2006/2007 Revised when the query is run there is a null value. To overcome the Null problem you will need to change this column to 2006/2007 Revised1: Sum(nz([2006/2007 Revised],0)) to give you a zero. In your report you will also need to change the occurences of 2006/2007 Revised to 2006/2007 Revised1.

In your query each column that is used a Sum you will need to change to the format as above. You will also need to change your report field names to the new names in the query.

May I suggest that you do not use a field name in the format of 2006/2007 Revised as this will cause a problem when you try to reference the field. You could use 20062007Revised or 2006_2007_revised etc. depending of your ease of recognising the field name.

 

[amunafc]

@Poppa Smurf thank you very much i will try it ASAP. John Big Booty gave me hip tips but did not work. try to download another database i have sent. this one now has no null values. everything is intact but it still show the text error.

 

[Poppa Smurf]

I assume your fields that are used for the totals are numeric. The easiest solution would be in your table set the default value for these fields to zero. When you add a record and the fields where you have not entered a value will be shown as zero in your query.

 

[amunafc]

@Poppa Smurf Please try to download it and run the report, after that analyze it with excel and see what will happen... Thank you for the contribution but i know it won't work.

 

[Poppa Smurf]

Why are you putting numbers in a text field and then trying to do calculations? The fields should have a data type as number.

 

[amunafc]

Poppa Smurf
I have tried all possible means but failed. First all the numerical fields had Number as data type but nothing happened. Remember i have uploaded the database, why cant you download it and work on it?

 

[Poppa Smurf]

I have been working on it THAT IS WHY I am asking the questions. Just because we respond does not mean that we are not looking at your database.

I have changed your example to data type number then exported the table to excel there were zeroes where there were no values entered.