Question Add Fields with True Value Only

[djossh]

How to write this in Expression builder? I want to add fields with "false (N)" value only. In my example below the total should be 12 only (adding 3+2+3+4=12).. Take Note that Y/N fields are constantly changing... Using IIF formula might be the answer but Im a newbie in access.. please help.. thanks


Y/N field=======Qty Field
[YN1]=N=========3 [Q1]
[YN2]=N=========2 [Q2]
[YN3]=Y=========8 [Q3]
[YN4]=N=========3 [Q4]
[YN5]=N=========4 [Q5]

 

[bob fitz]

Can you explain a little more about what you are trying to do?

Are you are trying to get the result in a query or a form?

 

[djossh]

Can you explain a little more about what you are trying to do?

Are you are trying to get the result in a query or a form?

I was trying to get it from a form.. this is what's on my mind..

If fields NY1, NY2, NY3, NY4 & NY5 is Equal to "N or (N for "NO" or false)" then sum fields Q1,Q2,Q3,Q4 & Q5 or should i say corresponding Q Fields..

I know it's been complicated I was getting confused also.. I think I need to normalized my form to get the right Fields and equations.

 

[bob fitz]

I think you really need to change your table structure, but in case that is not possible, I have a possible solution.
You would need a function to handle the calculation, which would then be used in a query that would be the Record Source of your form. You would also need a little code in form module.
If you need to try this solution, take a look at the attached db as an example and post back with any questions you may have.

 

[djossh]

I think you really need to change your table structure, but in case that is not possible, I have a possible solution.
You would need a function to handle the calculation, which would then be used in a query that would be the Record Source of your form. You would also need a little code in form module.
If you need to try this solution, take a look at the attached db as an example and post back with any questions you may have.

Thank you so much... I will surely try the code... and yes.. I already change my table structure. I really appreciate your Help.. Thanks..

 

[bob fitz]

Your welcome. Post back if you need more help with the example db, but if you've changed your tables there should be an easier way to get what you want.

 

[djossh]

Your welcome. Post back if you need more help with the example db, but if you've changed your tables there should be an easier way to get what you want.

Hi Bob.. I have another question. How to write the formula in Query "TotalField". here is the scenario... I want to count the number of "A" in Field1,Field2,Field3 & Field4 in Query..


Field1====Field2====Field3====Field4==TotalField
=A======____======A======____=====2

 

[bob fitz]

Hi Bob.. I have another question. How to write the formula in Query "TotalField". here is the scenario... I want to count the number of "A" in Field1,Field2,Field3 & Field4 in Query..

I'm not sure that that is possible. If it is maybe someone else will show us how. It could be done using a function but I wonder if this is another case of incorrect table structure?

 

[djossh]

I'm not sure that that is possible. If it is maybe someone else will show us how. It could be done using a function but I wonder if this is another case of incorrect table structure?

I think it can be done by IIF function. but I dont know how to write IFF using multiple fields.... example.. If(Field1=A,1,0)+ If(Field2=A,1,0)+ If(Field3=A,1,0)+ If(Field4=A,1,0)+ If(Field5=A,1,0)... I know it is not the right way of adding those 5 Fields.. Any Idea?? Thank for the reply...

 

[djossh]

I'm not sure that that is possible. If it is maybe someone else will show us how. It could be done using a function but I wonder if this is another case of incorrect table structure?

I think it can be done by IIF function. but I dont know how to write IFF using multiple fields.... example.. If(Field1=A,1,0)+ If(Field2=A,1,0)+ If(Field3=A,1,0)+ If(Field4=A,1,0)+ If(Field5=A,1,0)... I know it is not the right way of adding those 5 Fields.. Any Idea?? Thanks for the reply...

 

[bob fitz]

Using the IIF() function should give the required results. Try this:

TotalAs: IIf([Field1]="A",1,0)+IIf([Field2]="A",1,0)+IIf([Field3]="A",1,0)+IIf([Field4]="A",1,0)+IIf([Field5]="A",1,0)