Minggu, 16 Oktober 2011

RE: [MS_AccessPros] Having troubel with Sum function in a report

 

David-

It would be helpful to know where you're performing each of these calculations. Are any of them in the Record Source of the report or subreport? Where have you place Sum of PartCost1, txtTaxTotalMake, Sum of WorkRequesteedCost1, and txtMiscCostMake? On the report or the subreport? Note that you get prompted for txtPartsTotal because that control is calculated. You have to repeat the expression used in the Control Source of txtPartsTotal in your Sum.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of David Tolson
Sent: Sunday, October 16, 2011 2:03 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Having troubel with Sum function in a report

Thank you Crystal, I repeated the equation and the fields still end up blank. Also Access is prompting me for a parameter value for all of the fields where I repeat the equation. Here are the four fields that I am attempting to sum and their corresponding control sources:

[Sum of PartCost1] – Record Source: =Sum([Reports]![rpt4CustomerRepairOrders]![rptParts-RepairOrder subreport].[Report]![txtPartsTotal])

[txtTaxTotalMake] – Record Source: =Sum([txtPartsTotal]*0.06)

[Sum of WorkRequestedCost1] – Record Source: =Sum([Reports]![rpt4CustomerRepairOrders]![rptParts-RepairOrder subreport].[Report]![txtLaborTotal])

[txtMiscCostMake] – Record Source: =Sum([txtMiscCost])

Sum of PartCost1 is the sum of a field in a subreport that is not visible, but the proper amount is displayed in the detail field, and the same is true for Sum of WorkRequestedCost1.

Access is prompting me for a value for txtPartsTotal, txtLaborTotal, and txtMiscCost, although these fields display the proper amounts when the report opens on screen.

It may be helpful to know that the equation for txtPartsTotal has an IIF statement as does the equation for txtLaborTotal:

=IIf([rptParts-RepairOrder subreport].[Report].[HasData],[rptParts-RepairOrder subreport].[Report]![txtPartsTotal],0)

=IIf([rptRepair-RepairOrder subreport].[Report].[HasData],[rptRepair-RepairOrder subreport].[Report]![txtRepairItemTotal],0)

FYI, I also tried repeating the equation in the footer total with the IIF, and the fields gave me $0.00 instead of blanks like they did in the above record sources.

Thanks again for your help so far,

David

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Saturday, October 15, 2011 11:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Having troubel with Sum function in a report

hi David,

you cannot sum a control that has an equation -- repeat the equation

=sum( <equarion in txtPartsTotal>)

~Crystal

________________________________
From: djsdaddy531
Great Day, I have created a report that displays the revenue that a vehicle repair shop has made on servicing its customers' vehicles. The fields look something like this:

InvoiceDate
Cost of Parts
Tax
Service Cost
Misc. Cost

And then it sums these fields on each vehicle and each customer, since a customer can have more than one vehicle. Then it does a grand total for the report. For aesthetics I have placed the text boxes for the above referenced fields in the JobID Header (RepairOrder), and I am placing the total fields for the vehicle in the YearMakeModel footer and the total fields for the customer in the Customer Footer. In the JobId footer(repair order) I have the following control source for summing the Cost of parts field…

=Sum([txtPartsTotal])

and so forth for the other fields. txtPartsTotal is the field that grabs the sum total of parts used from a subreport. However, when the form opens (rptCustomerRevenue), a dialogue box is requesting the I enter a parameter value for txtPartsTotal, although txtPartsTotal displays the appropriate number.

I would greatly appreciate if someone can tell me why I am getting these parameter requests.

Thanks in advance.

r/David

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar