Jumat, 20 April 2012

RE: [MS_AccessPros] Retrieveing a sum from a subform

 

Art-

First, try:

=NZ(DSum("IncomeAmount", "tblApplicationHouseholdIncome", "PayFreqID = 6 AND
HouseholdID = " & [HouseHoldID]), 0) +
NZ((DSum("IncomeAmount", "tblApplicationHouseholdIncome", "PayFreqID = 3 AND
HouseHoldID = " & [HouseHoldID) * 12), 0)

And put a Me.Parent.Refresh in the AfterUpdate event of the subform.

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/
(Villefranche-sur-mer, France)

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

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Friday, April 20, 2012 3:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Retrieveing a sum from- a subform

John,

I also found that the my txtAnnualAmount unbound field does not change when I change the incomeamount field. Maybe I should move this off to the current event instead of putting the DSUM statement in the txtAnnualAmount record source.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, April 17, 2012 1:26 PM
Subject: RE: [MS_AccessPros] Retrieveing a sum from a subform

Art-

How about:

=DSum("IncomeAmount", "tblApplicationHouseholdIncome", "PayFreqID = 6 AND
HouseholdID = " & [HouseHoldID]) +
(DSum("IncomeAmount", "tblApplicationHouseholdIncome", "PayFreqID = 3 AND
HouseHoldID = " & [HouseHoldID) * 12)

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/
(Villefranche-sur-mer, France)

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

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Tuesday, April 17, 2012 2:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Retrieveing a sum from a subform

I have a mainform called frmHouseholdDetails that has a subform called
sfrmHouseholdIncomeList. The subformi is defined as a countinous form with the
record source of:
SELECT tblApplicationHouseholdIncome.IncomeID,
tblApplicationHouseholdIncome.HouseholdID, tlkpIncomeType.IncomeTypeName,
tblApplicationHouseholdIncome.IncomeAmount,
tblApplicationHouseholdIncome.VerificationDate, tlkpPayFreqType.PayFreqTypeName
AS [Payment Frequency], tlkpVerificationType.VerificationTypeName AS
[Verification Type]
FROM ((tblApplicationHouseholdIncome INNER JOIN tlkpIncomeType ON
tblApplicationHouseholdIncome.IncomeTypeID = tlkpIncomeType.IncomeTypeID) LEFT
JOIN tlkpVerificationType ON tblApplicationHouseholdIncome.VerificationTypeID =
tlkpVerificationType.VerfificationTypeID) LEFT JOIN tlkpPayFreqType ON
tblApplicationHouseholdIncome.PayFreqTypeID = tlkpPayFreqType.PayFreqTypeID;

On the mainform I have a textbox called txtAnnualTotal which I want to Sum the
IncomeAmount from the subform if the PayFreQID is 6 - Annual. If the PayFreqID =
3 - Monthly then I need to calculate it by time 12. Now here is the kicker, the
IncomeAmount in the datasheet might contain diffent PayfreqIDs so I need to
figure that out. Some one might get a set annual amount and they might also
recieve a monthly stipend.

Any ideas?

Thank you,

Art Lorenzini
Sioux Falls, SD

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

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar