using "yes/no" field to sort report data and create subtotals & to

[screensaver100]

I have a report that is being sorted by a "yes/no" field (named "production
run") to sort records by "produced" and "ordered" (ordered being "no"). My
report can sub-total the quantities by this criteria but I need to be able to
calculate "produced" minus "ordered" to get a "total on hand quantity" (the
report total). I can't seem to get the criteria correct in my unbound text
box in the report footer.

Any help would be appreciated.

 

[Duane Hookom]

Could you provide some actual field names and sample data with the expected
results?

 

[screensaver100]

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold items)

sorts by production run "yes" and gives a subtotal (in this case the
quantity of those 2 records are 5,200), then sorts production run "no" and
gives a sub-total (the quantity of those 3 records are 60). I need the
quantity of the no items to subtract from the yes items (a "total on hand" of
5,200 - 60 = 5140.

screensaver100

 

[Duane Hookom]

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

 

[screensaver100]

When I tried that (I copied and pasted from your response below) it says
"expression typed incorrectly or is too complex to be evaluated.
--
screensaver100

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold items)

sorts by production run "yes" and gives a subtotal (in this case the
quantity of those 2 records are 5,200), then sorts production run "no" and
gives a sub-total (the quantity of those 3 records are 60). I need the
quantity of the no items to subtract from the yes items (a "total on hand" of
5,200 - 60 = 5140.

screensaver100

 

[Duane Hookom]

I expect the data type of Production Run is Yes/No so it doesn't store "Yes",
it stores true/-1 or false/0 but might display "Yes" or "No". If so, try:

=Sum(IIf([Production Run],1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP

When I tried that (I copied and pasted from your response below) it says
"expression typed incorrectly or is too complex to be evaluated.
--
screensaver100

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold items)

sorts by production run "yes" and gives a subtotal (in this case the
quantity of those 2 records are 5,200), then sorts production run "no" and
gives a sub-total (the quantity of those 3 records are 60). I need the
quantity of the no items to subtract from the yes items (a "total on hand" of
5,200 - 60 = 5140.

screensaver100


:

Could you provide some actual field names and sample data with the expected
results?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is being sorted by a "yes/no" field (named "production
run") to sort records by "produced" and "ordered" (ordered being "no"). My
report can sub-total the quantities by this criteria but I need to be able to
calculate "produced" minus "ordered" to get a "total on hand quantity" (the
report total). I can't seem to get the criteria correct in my unbound text
box in the report footer.

Any help would be appreciated.

 

[Gina Whipp]

Did you mean...

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I expect the data type of Production Run is Yes/No so it doesn't store
"Yes",
it stores true/-1 or false/0 but might display "Yes" or "No". If so, try:

=Sum(IIf([Production Run],1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP

When I tried that (I copied and pasted from your response below) it says
"expression typed incorrectly or is too complex to be evaluated.
--
screensaver100

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP


:

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold
items)

sorts by production run "yes" and gives a subtotal (in this case the
quantity of those 2 records are 5,200), then sorts production run
"no" and
gives a sub-total (the quantity of those 3 records are 60). I need
the
quantity of the no items to subtract from the yes items (a "total on
hand" of
5,200 - 60 = 5140.

screensaver100


:

Could you provide some actual field names and sample data with the
expected
results?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is being sorted by a "yes/no" field (named
"production
run") to sort records by "produced" and "ordered" (ordered being
"no"). My
report can sub-total the quantities by this criteria but I need
to be able to
calculate "produced" minus "ordered" to get a "total on hand
quantity" (the
report total). I can't seem to get the criteria correct in my
unbound text
box in the report footer.

Any help would be appreciated.

 

[Duane Hookom]

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])
and
=Sum(IIf([Production Run],1,-1) * [Quantity])
should be the same. The first argument of the IIf() should be an expression
that returns either true or false. The Yes/No field by itself should return
either True or False.

--
Duane Hookom
Microsoft Access MVP

Did you mean...

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I expect the data type of Production Run is Yes/No so it doesn't store
"Yes",
it stores true/-1 or false/0 but might display "Yes" or "No". If so, try:

=Sum(IIf([Production Run],1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP

When I tried that (I copied and pasted from your response below) it says
"expression typed incorrectly or is too complex to be evaluated.
--
screensaver100


:

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP


:

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold
items)

sorts by production run "yes" and gives a subtotal (in this case the
quantity of those 2 records are 5,200), then sorts production run
"no" and
gives a sub-total (the quantity of those 3 records are 60). I need
the
quantity of the no items to subtract from the yes items (a "total on
hand" of
5,200 - 60 = 5140.

screensaver100


:

Could you provide some actual field names and sample data with the
expected
results?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is being sorted by a "yes/no" field (named
"production
run") to sort records by "produced" and "ordered" (ordered being
"no"). My
report can sub-total the quantities by this criteria but I need
to be able to
calculate "produced" minus "ordered" to get a "total on hand
quantity" (the
report total). I can't seem to get the criteria correct in my
unbound text
box in the report footer.

Any help would be appreciated.

 

[Gina Whipp]

DIdn't know that, always thought you had to specify.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])
and
=Sum(IIf([Production Run],1,-1) * [Quantity])
should be the same. The first argument of the IIf() should be an
expression
that returns either true or false. The Yes/No field by itself should
return
either True or False.

--
Duane Hookom
Microsoft Access MVP

Did you mean...

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I expect the data type of Production Run is Yes/No so it doesn't store
"Yes",
it stores true/-1 or false/0 but might display "Yes" or "No". If so,
try:

=Sum(IIf([Production Run],1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP


:

When I tried that (I copied and pasted from your response below) it
says
"expression typed incorrectly or is too complex to be evaluated.
--
screensaver100


:

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP


:

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold
items)

sorts by production run "yes" and gives a subtotal (in this case
the
quantity of those 2 records are 5,200), then sorts production run
"no" and
gives a sub-total (the quantity of those 3 records are 60). I need
the
quantity of the no items to subtract from the yes items (a "total
on
hand" of
5,200 - 60 = 5140.

screensaver100


:

Could you provide some actual field names and sample data with
the
expected
results?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is being sorted by a "yes/no" field
(named
"production
run") to sort records by "produced" and "ordered" (ordered
being
"no"). My
report can sub-total the quantities by this criteria but I
need
to be able to
calculate "produced" minus "ordered" to get a "total on hand
quantity" (the
report total). I can't seem to get the criteria correct in my
unbound text
box in the report footer.

Any help would be appreciated.

 

[screensaver100]

That worked fine.

Thanks very much for your help.
--
screensaver100

DIdn't know that, always thought you had to specify.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])
and
=Sum(IIf([Production Run],1,-1) * [Quantity])
should be the same. The first argument of the IIf() should be an
expression
that returns either true or false. The Yes/No field by itself should
return
either True or False.

--
Duane Hookom
Microsoft Access MVP

Did you mean...

=Sum(IIf([Production Run]=-1,1,-1) * [Quantity])


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I expect the data type of Production Run is Yes/No so it doesn't store
"Yes",
it stores true/-1 or false/0 but might display "Yes" or "No". If so,
try:

=Sum(IIf([Production Run],1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP


:

When I tried that (I copied and pasted from your response below) it
says
"expression typed incorrectly or is too complex to be evaluated.
--
screensaver100


:

Try something like:
=Sum(IIf([Production Run]="YES",1,-1) * [Quantity])

--
Duane Hookom
Microsoft Access MVP


:

Fields are:
Product Name (set to run only one product at a time)
Production Run = "yes or no"
PO Number = number field
Order Date
Quantity (this represents either the quantity of produced or sold
items)

sorts by production run "yes" and gives a subtotal (in this case
the
quantity of those 2 records are 5,200), then sorts production run
"no" and
gives a sub-total (the quantity of those 3 records are 60). I need
the
quantity of the no items to subtract from the yes items (a "total
on
hand" of
5,200 - 60 = 5140.

screensaver100


:

Could you provide some actual field names and sample data with
the
expected
results?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is being sorted by a "yes/no" field
(named
"production
run") to sort records by "produced" and "ordered" (ordered
being
"no"). My
report can sub-total the quantities by this criteria but I
need
to be able to
calculate "produced" minus "ordered" to get a "total on hand
quantity" (the
report total). I can't seem to get the criteria correct in my
unbound text
box in the report footer.

Any help would be appreciated.