sum two text fields and two checkboxes

  • Thread starter Thread starter BeaBea
  • Start date Start date
B

BeaBea

I have a report that I am trying to create and need some help on summing.
The fields I have on my report are: supervisor, product, product type,
ysnapproved and ysnnotapproved.

Supervisor: James

Product Product Type ysnapproved ysnnotapproved
Oil Pan IL (check box checked)
Oil Pan IL (check box checked)
Fan Belt IL
(check box checked)
Oil Pan OC
(check box checked)

I tried using: sum(IIF([product type]="IL" And [ysnapproved]="True",1,0) )
for the number of approved; and sum(IIF([product type]="IL" And
[ysnnotapproved]="True",1,0) ) for the number of products not approved. When
I run the report I only get some of the records for instance, if there are 20
records I only get a sum of 16. I tried grouping by Supervisor and by
product. How can I get the report to count all of the records? And I also
need a total for each row?
 
Why do you have two checkboxes for one logical condition?
Isn't either approved or not approved?

True and False are VBA contstants, not text values.
True = -1 and False = 0

If you are not getting all the records, it may be because of how you are
using the check boxes.

I would eliminate the ynnotapproved check box. You don't need it. Then use
this:
sum(IIF([product type]="IL" And [ysnapproved], 1 ,0))
and
sum(IIF([product type]="IL" And NOT [ysnapproved],1,0))

As to numbers for each row, you don't define what a row is or what you want
to include in that row.
 
I took your advice and removed the ysnnot approved checkbox and it worked. I
have just one other question. When I run the report if there are no records
for the product and the product type I want to have a 0 appear on the report.
How do I accomplish this.
--
Thank you,
BeaBea


Klatuu said:
Why do you have two checkboxes for one logical condition?
Isn't either approved or not approved?

True and False are VBA contstants, not text values.
True = -1 and False = 0

If you are not getting all the records, it may be because of how you are
using the check boxes.

I would eliminate the ynnotapproved check box. You don't need it. Then use
this:
sum(IIF([product type]="IL" And [ysnapproved], 1 ,0))
and
sum(IIF([product type]="IL" And NOT [ysnapproved],1,0))

As to numbers for each row, you don't define what a row is or what you want
to include in that row.

--
Dave Hargis, Microsoft Access MVP


BeaBea said:
I have a report that I am trying to create and need some help on summing.
The fields I have on my report are: supervisor, product, product type,
ysnapproved and ysnnotapproved.

Supervisor: James

Product Product Type ysnapproved ysnnotapproved
Oil Pan IL (check box checked)
Oil Pan IL (check box checked)
Fan Belt IL
(check box checked)
Oil Pan OC
(check box checked)

I tried using: sum(IIF([product type]="IL" And [ysnapproved]="True",1,0) )
for the number of approved; and sum(IIF([product type]="IL" And
[ysnnotapproved]="True",1,0) ) for the number of products not approved. When
I run the report I only get some of the records for instance, if there are 20
records I only get a sum of 16. I tried grouping by Supervisor and by
product. How can I get the report to count all of the records? And I also
need a total for each row?
Click to expand...
Click to expand...
 
Not really enough info to give a precise answer, but I am guessing that you
want the control where you do the sum to show 0.

Wrapping the sum in the Nz function should do it:

Nz(sum(IIF([product type]="IL" And [ysnapproved], 1 ,0)), 0)
--
Dave Hargis, Microsoft Access MVP


BeaBea said:
I took your advice and removed the ysnnot approved checkbox and it worked. I
have just one other question. When I run the report if there are no records
for the product and the product type I want to have a 0 appear on the report.
How do I accomplish this.
--
Thank you,
BeaBea


Klatuu said:
Why do you have two checkboxes for one logical condition?
Isn't either approved or not approved?

True and False are VBA contstants, not text values.
True = -1 and False = 0

If you are not getting all the records, it may be because of how you are
using the check boxes.

I would eliminate the ynnotapproved check box. You don't need it. Then use
this:
sum(IIF([product type]="IL" And [ysnapproved], 1 ,0))
and
sum(IIF([product type]="IL" And NOT [ysnapproved],1,0))

As to numbers for each row, you don't define what a row is or what you want
to include in that row.

--
Dave Hargis, Microsoft Access MVP


BeaBea said:
I have a report that I am trying to create and need some help on summing.
The fields I have on my report are: supervisor, product, product type,
ysnapproved and ysnnotapproved.

Supervisor: James

Product Product Type ysnapproved ysnnotapproved
Oil Pan IL (check box checked)
Oil Pan IL (check box checked)
Fan Belt IL
(check box checked)
Oil Pan OC
(check box checked)

I tried using: sum(IIF([product type]="IL" And [ysnapproved]="True",1,0) )
for the number of approved; and sum(IIF([product type]="IL" And
[ysnnotapproved]="True",1,0) ) for the number of products not approved. When
I run the report I only get some of the records for instance, if there are 20
records I only get a sum of 16. I tried grouping by Supervisor and by
product. How can I get the report to count all of the records? And I also
need a total for each row?
Click to expand...
Click to expand...
Click to expand...
 
Thank you, that worked great.
--
Thank you,
BeaBea


Klatuu said:
Not really enough info to give a precise answer, but I am guessing that you
want the control where you do the sum to show 0.

Wrapping the sum in the Nz function should do it:

Nz(sum(IIF([product type]="IL" And [ysnapproved], 1 ,0)), 0)
--
Dave Hargis, Microsoft Access MVP


BeaBea said:
I took your advice and removed the ysnnot approved checkbox and it worked. I
have just one other question. When I run the report if there are no records
for the product and the product type I want to have a 0 appear on the report.
How do I accomplish this.
--
Thank you,
BeaBea


Klatuu said:
Why do you have two checkboxes for one logical condition?
Isn't either approved or not approved?

True and False are VBA contstants, not text values.
True = -1 and False = 0

If you are not getting all the records, it may be because of how you are
using the check boxes.

I would eliminate the ynnotapproved check box. You don't need it. Then use
this:
sum(IIF([product type]="IL" And [ysnapproved], 1 ,0))
and
sum(IIF([product type]="IL" And NOT [ysnapproved],1,0))

As to numbers for each row, you don't define what a row is or what you want
to include in that row.

--
Dave Hargis, Microsoft Access MVP


:

I have a report that I am trying to create and need some help on summing.
The fields I have on my report are: supervisor, product, product type,
ysnapproved and ysnnotapproved.

Supervisor: James

Product Product Type ysnapproved ysnnotapproved
Oil Pan IL (check box checked)
Oil Pan IL (check box checked)
Fan Belt IL
(check box checked)
Oil Pan OC
(check box checked)

I tried using: sum(IIF([product type]="IL" And [ysnapproved]="True",1,0) )
for the number of approved; and sum(IIF([product type]="IL" And
[ysnnotapproved]="True",1,0) ) for the number of products not approved. When
I run the report I only get some of the records for instance, if there are 20
records I only get a sum of 16. I tried grouping by Supervisor and by
product. How can I get the report to count all of the records? And I also
need a total for each row?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top