Sum Help

[PRodgers4284]

Sum IF NOT IsNull Help

I have a formula for calculating value within a table field called Indication with the value "bef-pat":

Sum(IIf(Not IsNull([Indication1]),1,0))

i want to add another field and to the formula, for example

Sum(IIf(Not IsNull([Indication1] & [Indication2]),1,0))


I have textbox that displays the result but it not working properly, I have tested the formula with value in both the fields but it only outputs the number "1" in the textbox where it should be "2"

Can anyone help?

 

[Rabbie]

Looking at your IIF statement 1 would seem to be the correct answer. Why do you expect it to be 2?

 

[PRodgers4284]

Looking at your IIF statement 1 would seem to be the correct answer. Why do you expect it to be 2?

Rabbie i want the output to sum both the indication1 and indication2 fields if they do not contain a null value, so if indication 1 does not contain a null value the sum is 1 then if the indication2 field also does not contain a null value the sum is 1, i want the two values added together to output the value 2 in the total textbox if that makes sense. I will be using the total later to calculate an overall percentage based on the values entered in the form.

 

[PRodgers4284]

Looking at your IIF statement 1 would seem to be the correct answer. Why do you expect it to be 2?

I have this working, but the code same quite long:

=Sum(IIf(Not IsNull([Indication1]),1,0))+Sum(IIf(Not IsNull([Indication2]),1,0))+Sum(IIf(Not IsNull([Indication3]),1,0))

Is this the correct way to do this?

 

[Brianwarnock]

I cannot think of a simple smart alec way to do it but I would have switched my test and result round to save typing.
IIf(IsNull([Indication1]),0,1)

Brian

 

[PRodgers4284]

I cannot think of a simple smart alec way to do it but I would have switched my test and result round to save typing.
IIf(IsNull([Indication1]),0,1)

Brian

Thanks for that brian, im having a problem with the formula, when i add another record to the database the formula add together the new record totals with the previous record totals. Is there anything i can do to stop this from happening, my test database is below:

 

[Brianwarnock]

You are I believe trying to store calculated values into a table. That is not considered to be good DB design practice. You should calculate the values as needed for reports and forms.
I have added to your DB
My own Table
a query showing the calculations, and a Form bound to that query.
A Form bound to the table to show how you could calculate the info on a form without using a query, I always use queries, look at the Form current event and the Dble click events of both Totals text boxes.

I of course do not know what your aims are, this work is merely illustrative.

Brian

 

[PRodgers4284]

You are I believe trying to store calculated values into a table. That is not considered to be good DB design practice. You should calculate the values as needed for reports and forms.
I have added to your DB
My own Table
a query showing the calculations, and a Form bound to that query.
A Form bound to the table to show how you could calculate the info on a form without using a query, I always use queries, look at the Form current event and the Dble click events of both Totals text boxes.

I of course do not know what your aims are, this work is merely illustrative.

Brian

Brian i have added another field to the table and query called Total, i have added the calculation to the query which adds the indcount and the countaction together and it works fine, how would i add the Total calculation to the form?

 

[PRodgers4284]

Brian i have added another field to the table and query called Total, i have added the calculation to the query which adds the indcount and the countaction together and it works fine, how would i add the Total calculation to the form?

I got this sorted, thanks