how to calculate textboxes in a report?

[behedwin]

I have a report with 7 textboxes that are populated from a query.

I have created a 8th textbox where i want to sum up the textboxes.

the first 7 textboxes are short text in the table.
the 8th textbox i want to write a formula so it takes the current value of the 7 textboxes and show me a total value
textbox 1 + textbox 2 + textbox 3 + textbox 4...........


how do i do this?
it does not work to just type in the data field
=[textbox1]+[textbox2].....
i get no errors and no result

 

[welshgasman]

How does a user delete a value from a report?
If it is from the form for the table, then you could try either NZ function or an IIF(textbox="",0,textbox) for the query.
However it would be better to put a 0 back in the form if they delete the value?

 

[sheetspread]

Dont get it...
googled around on how to set the default value on a textbox, but it seems i cant?!

and if i look in my table the default value of the record is already set to 0

but when i calculate the textboxes i get no answer if one of them is empty.

I have no Access right now but I remember doing this in the past. Even if the reports textbox doesn't have a place to set a default value you can enter one in the table/query for it to carry over.

You can have validation rules to prevent users from blanking out the zeroes too.

 

[behedwin]

I really appreciate your answer, but i can not find an solution

 

[sheetspread]

Did you set the defaults to zero in the table/query?

 

[behedwin]

How do i set default zero in a query?
I have set default zero in the table....

But i need the calculation to work when any textbox is empty/null....

 

[welshgasman]

We are going around in circles here.
Default of zero in the table only means that a new record will start with a value of zero.
If you are allowing the users to delete the value in the control, then that will be written to the table. You could set the property to Required so that it has to have something, even if just a zero.

So I think you need to correct the source data, not try and fix it in the report?

I would inspect the table and see what data you do have. It would however be easy enough to make the field in the query zero if empty with something along the lines of

IIF([field]="",0,[field])

You might need to test for NULL as well depending on your tables? You could expand the above with another IIF in the formula or write a function to do it.

If you are doing it in the query, then I believe you would need another query with the first as the source if you were going to sum in the query.

All this is really 'patching' the problem, not fixing it. I believe you should fix your data.

 

[behedwin]

I would love to fix my data.
But this is a newly created table and i only have two records in it for testing.
And all fields are populated... those that are empty have an 0 in them.
But i forsee the problem that users might delete a zero. So i would like to force the zero back and not have to put the field as requierd.

I tried to put your code in the query

Could i solve this by writing VBA code in each textbox?
If field is null/empty/"", then put 0...

How to write something like that?

read allot about NZ function... but i dont understand how to implement it.

Your code for the query i dont know how to implement either... when i put this in the query it filters the query so it stops working.
I bet im doing it wrong.....

IIF([field]="",0,[field])

 

[welshgasman]

I suppose you could put code in the BeforeUpddate event of the form to put a zero back in a control if it is empty, as it would then not be NULL, just a ZLS

You have to replace [Field] with your field name in my code example ?

Another example

SELECT [2010 Transactions].ID, IIf([Amount]>100,"Yes","No") AS Expr1, [2010 Transactions].Amount, IIf([Amount]>100,[Amount],0) AS Expr2
FROM [2010 Transactions];

HTH

 

[sheetspread]

How do i set default zero in a query?
I have set default zero in the table....

But i need the calculation to work when any textbox is empty/null....

If you've set it in the table then no nulls should be present in either the query or report (???)

If you want to keep the nulls then I guess welshgasman's idea is the way to go

 

[xenou]

You can handle this easily in the query too.