Zero's won't add

[jnpeanut]

I have four fields in my table that I add together in my report. (Bill1, Bill2, Bill3, and Bill4) When thier are four bills my report looks good but if thier less than 4 bills it shows zero's where the other bill prices would be if their were any. {example below}

Good...........................................Bad
Bill 1 $6.00......................................Bill 1 $6.00
Bill 2 $3.00...................................................$0.00
Bill 3 $2.00...................................................$0.00
Bill 4 $3.00...................................................$0.00

Total $14.00 Total

So as you can see it doesn't add up my total if I have less than four bills and it also shows the zero's. HELP!!!

thanks very much

Justin

 

[trucktime]

Totals

Perhaps you can upload the database, and someone will have a look at it and see what the problem is.

 

[jnpeanut]

documents attached

I attached some documents to look at because it woulndn't let me attach a database.

Thanks

 

[trucktime]

Totals

Sorry, I really can't tell what the problem is.
Perhaps it has to do with the grouping in the report or a query if you use one.
Can you upload an empty database?

 

[jnpeanut]

It ony lets you upload certain file extentions like doc but no mdb.

 

[trucktime]

Upload

Yeah, you have to make it a zip file and keep it under 100k.

 

[jnpeanut]

I zipped it up

Here you go I zipped it up

 

[trucktime]

Totals

Since you have 4 fixed Charge fields on your report they will show, since their devault value = 0, so they have a value.
You probably can use the IIf function to hide the 0.
Haven't been able to figure that one out.

The problem you may encounter is this: if you have to bill the client more than 4 charges, what do you do? Adding another record for the client DRTABLE would create redundant data and will certainly create problems maintaining your database.
It would probably be better to create a second table for the bills and link it to the main table. When you use a main form for the client info, and a subform for the bill info you can ad as many charges to a client as needed.

Just a thought, sorry I can't be of more help.

 

[Liv Manto]

use the nz.

if your sum is bill1 +bill2 + etc

use:


=nz([bill1],0)+nz([bill2],0) + etc

 

[jnpeanut]

The nz worked great!! Thanks a lot.

 

[KenHigg]

Justin, I just browsed through your sample db an noticed some serious normalization issues. If this is close to your production db, you should really reconsider your table set up...

Just a thought
Ken