SUBTRACT true ckbx value in report

[lmv]

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form. This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the value of
the total value of the true donation ck boxes from the Cat footer and showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event on
the report if so where and what would the VBA code be? Please help by being
specific in the details on how... thanks!!

 

[Douglas J. Steele]

True values are stored as -1, False values are stored as 0. That means that
if you multiple the donation amount by the value of the Yes/No field and add
the resultant product, you'll get the (negative) sum of all donations.

 

[Wayne-I-M]

Hi

I'm a little lost with your questions but will have a go (even with no
"coloured circles"??)

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form.

I take it from this that you have a check box that if checked will include
the the amount in the orderdetails sum (in the footer). So if not checked
the amount will not be count - a type of IIIF([chkbox]=0,"add","don't")
that sort of thing.

This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.

OK - you either want to add up all the values in the orderdetails even if
the check box is or insn't checked - hope I'm getting this.
In this case add another text box to your footer and simple use a sum
=sum([whatever])

If you want to total the amounts that have a check then you could use the
numerical value of the check box to help.
Check box ticked = -1
Not ticked = 0
So you could times the total amounts in orderdetails by the check box and
this would ethier give you a possitive or negative amount (this is done in
the Details section - not the footer).
You then do a sum of this amount (negative and possitive) and this will give
the total you are looking for.

As I said - bit lost with the question so I may going down the wrong line
altogether.

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form. This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the value of
the total value of the true donation ck boxes from the Cat footer and showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event on
the report if so where and what would the VBA code be? Please help by being
specific in the details on how... thanks!!

 

[lmv]

Thanks for the response....
I do not know HOW to write the following code. Can someone give me an
example of how to write it.

If the yes/no field -1 then total the value of all of the yes/no fields then
subtract from the extendedprice

Then tell me where to put the code.

ie Wayne said:

if you multiple the donation amount by the value of the Yes/No field and add
the resultant product, you'll get the (negative) sum of all donations.

Can you give me an example of how the above is written?

ie Wayne said:

a type of IIIF([chkbox]=0,"add","don't") that sort of thing

I don't know what the above does exactly nor do I know where it should be put.

Thanks!

Hi

I'm a little lost with your questions but will have a go (even with no
"coloured circles"??)

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form.

I take it from this that you have a check box that if checked will include
the the amount in the orderdetails sum (in the footer). So if not checked
the amount will not be count - a type of IIIF([chkbox]=0,"add","don't")
that sort of thing.

This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.

OK - you either want to add up all the values in the orderdetails even if
the check box is or insn't checked - hope I'm getting this.
In this case add another text box to your footer and simple use a sum
=sum([whatever])

If you want to total the amounts that have a check then you could use the
numerical value of the check box to help.
Check box ticked = -1
Not ticked = 0
So you could times the total amounts in orderdetails by the check box and
this would ethier give you a possitive or negative amount (this is done in
the Details section - not the footer).
You then do a sum of this amount (negative and possitive) and this will give
the total you are looking for.

As I said - bit lost with the question so I may going down the wrong line
altogether.

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form. This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the value of
the total value of the true donation ck boxes from the Cat footer and showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event on
the report if so where and what would the VBA code be? Please help by being
specific in the details on how... thanks!!

 

[Douglas J. Steele]

Instead of =Sum([ExtendedPrice]), use =Sum([DonationCheckbox] *
Nz([DonationAmount]), or whatever your fields are named.

Alternatively, =Sum(IIf([DonationCheckbox],Nz([DonationAmount],0),0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the response....
I do not know HOW to write the following code. Can someone give me an
example of how to write it.

If the yes/no field -1 then total the value of all of the yes/no fields
then
subtract from the extendedprice

Then tell me where to put the code.

ie Wayne said:

if you multiple the donation amount by the value of the Yes/No field and
add
the resultant product, you'll get the (negative) sum of all donations.

Can you give me an example of how the above is written?

ie Wayne said:

a type of IIIF([chkbox]=0,"add","don't") that sort of thing

I don't know what the above does exactly nor do I know where it should be
put.

Thanks!

Hi

I'm a little lost with your questions but will have a go (even with no
"coloured circles"??)

I have a form that has a donation ck box. If the amount in the
orderdetails
unit price field is a donation it still totals in my "total" viewed in
the
form.

I take it from this that you have a check box that if checked will
include
the the amount in the orderdetails sum (in the footer). So if not
checked
the amount will not be count - a type of IIIF([chkbox]=0,"add","don't")
that sort of thing.

This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in
the
donation ckbox.

OK - you either want to add up all the values in the orderdetails even if
the check box is or insn't checked - hope I'm getting this.
In this case add another text box to your footer and simple use a sum
=sum([whatever])

If you want to total the amounts that have a check then you could use the
numerical value of the check box to help.
Check box ticked = -1
Not ticked = 0
So you could times the total amounts in orderdetails by the check box and
this would ethier give you a possitive or negative amount (this is done
in
the Details section - not the footer).
You then do a sum of this amount (negative and possitive) and this will
give
the total you are looking for.

As I said - bit lost with the question so I may going down the wrong line
altogether.

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do

I have a form that has a donation ck box. If the amount in the
orderdetails
unit price field is a donation it still totals in my "total" viewed in
the
form. This is fine. But when I do total budget report I want to have a
field
at the end of the report that subtracts the value of any TRUE value in
the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the
value of
the total value of the true donation ck boxes from the Cat footer and
showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event
on
the report if so where and what would the VBA code be? Please help by
being
specific in the details on how... thanks!!

 

[lmv]

Here is the qry:
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice,
qryPurchaseOrderRpt.Donation, qryPurchaseOrderRpt.UnitPrice,
Sum(IIf([Donation],Nz([UnitPrice],0),0)) AS DonationPrice
FROM qryPurchaseOrderRpt
WHERE (((qryPurchaseOrderRpt.ProjectID) Like [Enter 4 Letter Project ID?] &
"*"));
------------------------------------
Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function
Tried:
DonationPrice: Sum(IIf([Order Details].[Donation],Nz([Order
Details].[UnitPrice],0),0))

=Sum(IIf([Order Details].[Donation] * Nz([Order Details].[UnitPrice],0),0))

I get errors if I enter what you gave me with only one ) at the end as you
have.

Donation is a yes/no ck box field

So, doesn't it need to somehow reference if it is true (checked) then the
unitprice needs to be totalled.

Then in my last extendedprice field it needs to subtract the donationprice
field which should be an expression in the qry?

Sorry if this isn't clear.
Thanks

Instead of =Sum([ExtendedPrice]), use =Sum([DonationCheckbox] *
Nz([DonationAmount]), or whatever your fields are named.

Alternatively, =Sum(IIf([DonationCheckbox],Nz([DonationAmount],0),0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the response....
I do not know HOW to write the following code. Can someone give me an
example of how to write it.

If the yes/no field -1 then total the value of all of the yes/no fields
then
subtract from the extendedprice

Then tell me where to put the code.

ie Wayne said:

if you multiple the donation amount by the value of the Yes/No field and
add
the resultant product, you'll get the (negative) sum of all donations.

Can you give me an example of how the above is written?

ie Wayne said:

a type of IIIF([chkbox]=0,"add","don't") that sort of thing

I don't know what the above does exactly nor do I know where it should be
put.

Thanks!

Hi

I'm a little lost with your questions but will have a go (even with no
"coloured circles"??)

I have a form that has a donation ck box. If the amount in the
orderdetails
unit price field is a donation it still totals in my "total" viewed in
the
form.
I take it from this that you have a check box that if checked will
include
the the amount in the orderdetails sum (in the footer). So if not
checked
the amount will not be count - a type of IIIF([chkbox]=0,"add","don't")
that sort of thing.

This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in
the
donation ckbox.
OK - you either want to add up all the values in the orderdetails even if
the check box is or insn't checked - hope I'm getting this.
In this case add another text box to your footer and simple use a sum
=sum([whatever])

If you want to total the amounts that have a check then you could use the
numerical value of the check box to help.
Check box ticked = -1
Not ticked = 0
So you could times the total amounts in orderdetails by the check box and
this would ethier give you a possitive or negative amount (this is done
in
the Details section - not the footer).
You then do a sum of this amount (negative and possitive) and this will
give
the total you are looking for.

As I said - bit lost with the question so I may going down the wrong line
altogether.

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a form that has a donation ck box. If the amount in the
orderdetails
unit price field is a donation it still totals in my "total" viewed in
the
form. This is fine. But when I do total budget report I want to have a
field
at the end of the report that subtracts the value of any TRUE value in
the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the
value of
the total value of the true donation ck boxes from the Cat footer and
showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event
on
the report if so where and what would the VBA code be? Please help by
being
specific in the details on how... thanks!!

 

[David F Cox]

I do not see, and probably Access does not either, what group of records
you are summing donations for.

I would expect a group by purchase order clause in there.

Here is the qry:
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice,
qryPurchaseOrderRpt.Donation, qryPurchaseOrderRpt.UnitPrice,
Sum(IIf([Donation],Nz([UnitPrice],0),0)) AS DonationPrice
FROM qryPurchaseOrderRpt
WHERE (((qryPurchaseOrderRpt.ProjectID) Like [Enter 4 Letter Project ID?]
&
"*"));
------------------------------------
Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function
Tried:
DonationPrice: Sum(IIf([Order Details].[Donation],Nz([Order
Details].[UnitPrice],0),0))

=Sum(IIf([Order Details].[Donation] * Nz([Order
Details].[UnitPrice],0),0))

I get errors if I enter what you gave me with only one ) at the end as you
have.

Donation is a yes/no ck box field

So, doesn't it need to somehow reference if it is true (checked) then the
unitprice needs to be totalled.

Then in my last extendedprice field it needs to subtract the donationprice
field which should be an expression in the qry?

Sorry if this isn't clear.
Thanks

Instead of =Sum([ExtendedPrice]), use =Sum([DonationCheckbox] *
Nz([DonationAmount]), or whatever your fields are named.

Alternatively, =Sum(IIf([DonationCheckbox],Nz([DonationAmount],0),0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the response....
I do not know HOW to write the following code. Can someone give me an
example of how to write it.

If the yes/no field -1 then total the value of all of the yes/no fields
then
subtract from the extendedprice

Then tell me where to put the code.

ie Wayne said:
if you multiple the donation amount by the value of the Yes/No field
and
add
the resultant product, you'll get the (negative) sum of all donations.

Can you give me an example of how the above is written?

ie Wayne said:
a type of IIIF([chkbox]=0,"add","don't") that sort of thing

I don't know what the above does exactly nor do I know where it should
be
put.

Thanks!


:

Hi

I'm a little lost with your questions but will have a go (even with no
"coloured circles"??)

I have a form that has a donation ck box. If the amount in the
orderdetails
unit price field is a donation it still totals in my "total" viewed
in
the
form.
I take it from this that you have a check box that if checked will
include
the the amount in the orderdetails sum (in the footer). So if not
checked
the amount will not be count - a type of
IIIF([chkbox]=0,"add","don't")
that sort of thing.

This is fine. But when I do total budget report I want to have a
field
at the end of the report that subtracts the value of any TRUE value
in
the
donation ckbox.
OK - you either want to add up all the values in the orderdetails even
if
the check box is or insn't checked - hope I'm getting this.
In this case add another text box to your footer and simple use a sum
=sum([whatever])

If you want to total the amounts that have a check then you could use
the
numerical value of the check box to help.
Check box ticked = -1
Not ticked = 0
So you could times the total amounts in orderdetails by the check box
and
this would ethier give you a possitive or negative amount (this is
done
in
the Details section - not the footer).
You then do a sum of this amount (negative and possitive) and this
will
give
the total you are looking for.

As I said - bit lost with the question so I may going down the wrong
line
altogether.

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a form that has a donation ck box. If the amount in the
orderdetails
unit price field is a donation it still totals in my "total" viewed
in
the
form. This is fine. But when I do total budget report I want to have
a
field
at the end of the report that subtracts the value of any TRUE value
in
the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the
Donation
expression that will accomplish the extended price subtracting the
value of
the total value of the true donation ck boxes from the Cat footer
and
showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an
event
on
the report if so where and what would the VBA code be? Please help
by
being
specific in the details on how... thanks!!

 

[Tom Wickerath]

Hi Imv,

I'll take a stab at helping you with this, based on your request from
earlier today
(http://www.microsoft.com/office/com...cess&mid=8fa263f8-2515-4b49-8a7f-e7ad543c1c35)

First, it's not clear to me whether or not you even need to show the
donation amounts anywhere on the report, since you seem to want to exclude
them from a totals shown in the footer of your report. If this is true, then
simply include a criteria in the query to filter out records where donation
checked is true.

On the other hand, if you need to show the donation amount in the report
(such as in the detail section), but exclude it from being included in a
summed ExtendedPrice textbox in the footer section, then I would think that
something like this might work, but I have not spent the time to set up a
test to verify this first:

=Sum([ExtendedPrice]) + Sum([ExtendedPrice]*[Donation])

If Donation is unchecked (False), the value will be zero, so you'll be
adding zero times the Extended price. If Donation is checked (True), the
value will be negative 1, so you'll be adding a negative summed amount.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[lmv]

Tom,

First, it's not clear to me whether or not you even need to show the
donation amounts anywhere on the report,

Yes I want to show the donations at the end so we can see how much it would
have cost before we deduct the donations. Right now the donation ck box
doesn't affect the unitprice in the order details. The Unitprice still gives
a total on each PO including the donated amounts. But, when I do a report I
want to show a price then show the donated amt and then show what sum as you
said.

=Sum([ExtendedPrice]) + Sum([ExtendedPrice]*[Donation])

here is a qry that does part of what I want
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice,
qryPurchaseOrderRpt.Donation
FROM qryPurchaseOrderRpt
WHERE (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID? Example:LEBA]
& "*") AND ((qryPurchaseOrderRpt.Donation)=True));

This is just a qry I was trying that gives me only a list of the unitprices
I need to total and then subtract...

when I try adding into the qry an expression

Donation Amt: Sum([ExtendedPrice])+Sum([ExtendedPrice]*[Donation])

Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function

Before I Tried:
DonationPrice: Sum(IIf([Order Details].[Donation],Nz([Order
Details].[UnitPrice],0),0))

=Sum(IIf([Order Details].[Donation] * Nz([Order Details].[UnitPrice],0),0))

If Donation is unchecked (False), the value will be zero, so you'll be
adding zero times the Extended price. If Donation is checked (True), the
value will be negative 1, so you'll be adding a negative summed amount.

I understand the concept I just don't know how to make a DonationAmt
expression that will take the true Donation unitprices and total them as an
expression in the qry for the report.

Thanks for your help...

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form. This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the value of
the total value of the true donation ck boxes from the Cat footer and showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event on
the report if so where and what would the VBA code be? Please help by being
specific in the details on how... thanks!!

 

[Tom Wickerath]

Hi Imv,

when I try adding into the qry an expression

Donation Amt: Sum([ExtendedPrice])+Sum([ExtendedPrice]*[Donation])

Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function

My suggestion did not have you add the expression to the query. Instead, add
the calculation to the textbox in the footer of your report.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom,

First, it's not clear to me whether or not you even need to show the
donation amounts anywhere on the report,

Yes I want to show the donations at the end so we can see how much it would
have cost before we deduct the donations. Right now the donation ck box
doesn't affect the unitprice in the order details. The Unitprice still gives
a total on each PO including the donated amounts. But, when I do a report I
want to show a price then show the donated amt and then show what sum as you
said.

=Sum([ExtendedPrice]) + Sum([ExtendedPrice]*[Donation])

here is a qry that does part of what I want
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice,
qryPurchaseOrderRpt.Donation
FROM qryPurchaseOrderRpt
WHERE (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID? Example:LEBA]
& "*") AND ((qryPurchaseOrderRpt.Donation)=True));

This is just a qry I was trying that gives me only a list of the unitprices
I need to total and then subtract...

when I try adding into the qry an expression

Donation Amt: Sum([ExtendedPrice])+Sum([ExtendedPrice]*[Donation])

Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function

Before I Tried:
DonationPrice: Sum(IIf([Order Details].[Donation],Nz([Order
Details].[UnitPrice],0),0))

=Sum(IIf([Order Details].[Donation] * Nz([Order Details].[UnitPrice],0),0))

If Donation is unchecked (False), the value will be zero, so you'll be
adding zero times the Extended price. If Donation is checked (True), the
value will be negative 1, so you'll be adding a negative summed amount.

I understand the concept I just don't know how to make a DonationAmt
expression that will take the true Donation unitprices and total them as an
expression in the qry for the report.

Thanks for your help...

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form. This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the value of
the total value of the true donation ck boxes from the Cat footer and showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event on
the report if so where and what would the VBA code be? Please help by being
specific in the details on how... thanks!!

 

[lmv]

That is the answer... yippee
THANK YOU SOOOOOOOOOOOO MUCH! Sometimes I make it too complicated I think!

Hi Imv,

when I try adding into the qry an expression

Donation Amt: Sum([ExtendedPrice])+Sum([ExtendedPrice]*[Donation])

Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function

My suggestion did not have you add the expression to the query. Instead, add
the calculation to the textbox in the footer of your report.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom,

First, it's not clear to me whether or not you even need to show the
donation amounts anywhere on the report,

Yes I want to show the donations at the end so we can see how much it would
have cost before we deduct the donations. Right now the donation ck box
doesn't affect the unitprice in the order details. The Unitprice still gives
a total on each PO including the donated amounts. But, when I do a report I
want to show a price then show the donated amt and then show what sum as you
said.

=Sum([ExtendedPrice]) + Sum([ExtendedPrice]*[Donation])

here is a qry that does part of what I want
SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
qryPurchaseOrderRpt.MainCatID, qryPurchaseOrderRpt.CategoryName,
qryPurchaseOrderRpt.MainCatName, qryPurchaseOrderRpt.ExtendedPrice,
qryPurchaseOrderRpt.Donation
FROM qryPurchaseOrderRpt
WHERE (((qryPurchaseOrderRpt.ProjectID) Like [What Project ID? Example:LEBA]
& "*") AND ((qryPurchaseOrderRpt.Donation)=True));

This is just a qry I was trying that gives me only a list of the unitprices
I need to total and then subtract...

when I try adding into the qry an expression

Donation Amt: Sum([ExtendedPrice])+Sum([ExtendedPrice]*[Donation])

Error is: tried to execute qry that does not include the specified
expression "project Id" as part of an aggregate function

Before I Tried:
DonationPrice: Sum(IIf([Order Details].[Donation],Nz([Order
Details].[UnitPrice],0),0))

=Sum(IIf([Order Details].[Donation] * Nz([Order Details].[UnitPrice],0),0))

If Donation is unchecked (False), the value will be zero, so you'll be
adding zero times the Extended price. If Donation is checked (True), the
value will be negative 1, so you'll be adding a negative summed amount.

I understand the concept I just don't know how to make a DonationAmt
expression that will take the true Donation unitprices and total them as an
expression in the qry for the report.

Thanks for your help...

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a form that has a donation ck box. If the amount in the orderdetails
unit price field is a donation it still totals in my "total" viewed in the
form. This is fine. But when I do total budget report I want to have a field
at the end of the report that subtracts the value of any TRUE value in the
donation ckbox.
I have one report that totals the donations. That works fine.
My problem is on my Budget
The report is based on a qry... I don't know how to write the Donation
expression that will accomplish the extended price subtracting the value of
the total value of the true donation ck boxes from the Cat footer and showing
it in the project footer

In the cat ID footer I have a =Sum([ExtendedPrice]) field

report name: rptBudgetWODetails
qry name: qryBudgetUpdate

In the projectID footer I have a =Sum([ExtendedPrice]) field
Donation ck box name : bxDonation

table where info is found: Order Details
field: Donations
field: unitPrice

Can someone tell me what to put in the qry or should it go in an event on
the report if so where and what would the VBA code be? Please help by being
specific in the details on how... thanks!!

 

[Tom Wickerath]

Hi Imv,

I'm very glad I could help! Your response makes it all worth it!!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________