Discussion Locked

No value stops the formula in Access Rpt

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a database that is full of work orders, and I need to run a report that will list the truck and the repair total in the corect category (eg..Tire repairs in the tire column and lights in the light column).
The problem I am having is if the truck did not have any work done in a category it should be zero and should not affect the total. Now if a filed has no value my total field will not add all the values if one is empty unless I remove the blank field. How can I add the fields together even when one has no value. This is in Access 2000 and I am using a query for each category that totals that categorys repair for each truck.
Then I combine the querys to build the reports. I have manually checked the figures and they are correct but I cannot get the field to add if one is blank. all of the filed formats in the report are currency and changeing the format does not seem to make a difference.
Dennis

8 replies

by glen_mcleod

Instead of manipulating all these values in the report, why don't you base your reports on queries that return the correct data? It's much easier to disregard a null field in a query using an aggregate function (sum, count, ...) than to do the samemanually in a report.

Glen

by dennisbv

I created a query and put in the formula to add the four fields and it would only work if there was a value in all four of the fields. I am not sure on how to accolplish what you are sugesting

by fofa

If you don't want to do #1 (the best solution) you can set a default value for the column, and then write a query to set all the null values to that default. Then when new records are added they will be defaulted to the same value. At least you can do away with the nulls that way.

by dennisbv

I don't really want to reject your answer or the first one but in order for me to post a comment. I want the best answer but some times the answer is not clear to someone that only does this stuff part time. I am basing my report on queries, I get all my category totals using a query with a group by Sum. I have tryed to set the default in the text boxes on the form. The table that all this information is pulled from there are no null fields.

by dennisbv

I don't really want to reject your answer or the first one but in order for me to post a comment I need to rate it. I want the best answer but some times the answer is not clear to someone that only does this stuff part time. I am basing my report on queries, I get all my category totals using a query with a group by Sum. I have tryed to set the default in the text boxes on the form. The table that all this information is pulled from there are no null fields.

by it_cowboy

This is a very important lesson to learn, about null values that is. There are several ways to get around it.

In the Access Report
Instead of displaying the field that may contain a null value, add a text box and enter the following code into it.

Assuming that the field is called TruckTotal...

=Iif(IsNull([TruckTotal]),0,[TruckTotal])

What this says that if the TruckTotal field is null, show Zero, otherwise show whatever the TruckTotal field value is.

Another method is to create fields in your Query with exactly the same formula (this is probably the better way).

ie: TruckTot: Iif(IsNull([TruckTotal]),0,[TruckTotal])

Then insert the TruckTot field into your report.

Good Luck

by dennisbv

Thanks. This is exactly what I needed. I am really trying to bring myself up to speed on Access programing. So complete answers like this are great.

This discussion is closed to new replies.