Dsum function with three Criteria (1 Viewer)

[IngressBusiness]

Hullo Friends , a trying to Sum up school fees payments in a query where by i need to add a given class school fees in a given term and year
am trying to use the Dsum function, but am falling to make it

This is what i have
Class school fees
school term
school year
=DSum("Amount","Payments","Class ='" & [Class] & and "Class ='" & [Term]and "Class ='" & [Year]"'")
Can some one please help?
Thanks

 

[June7]

=DSum("Amount", "Payments", "Class ='" & [Class] & "' AND Term ='" & [Term] & "' AND Year ='" & [Year] & "'")

Domain aggregate functions can cause slow performance in query. Why don't you build aggregate query? Or build a report and use Sorting & Grouping feature with aggregate calculation in textbox? This allows display of detail data as well as summary calcs.

Why is Year a text value? Year is a reserved word and really should not use reserved words as names.

 

[IngressBusiness]

=DSum("Amount", "Payments", "Class ='" & [Class] & "' AND Term ='" & [Term] & "' AND Year ='" & [Year] & "'")

Domain aggregate functions can cause slow performance in query. Why don't you build aggregate query?

Why is Year a text value? Year is a reserved word and really should not use reserved words as names.

Thanks actually when i it in the querry its like this.

TermFees: DSum("Amount","Payments","Class ='" & [Class] & "' AND Term ='" & [Term] & "' AND YearDatemade ='" & [YearDatemade] & "'")

But it doesn't work

 

[June7]

What happens? Is YearDateMade a text or number field? If number, don't use apostrophe delimiters.

Syntax is now correct but the DSum() is not really making sense.

Does Payments table have fields Class, Term, YearDateMade?

What table is the query pulling from? Show the SQL statement.

 

[IngressBusiness]

Well

What happens? Is YearDateMade a text or number field? If number, don't use apostrophe delimiters.

Syntax is now correct but the DSum() is not really making sense.

Does Payments table have fields Class, Term, YearDateMade?

What table is the query pulling from? Show the SQL statement.

well, the YearDatemade is a calculted field from date of payment

the SQL id like below:

SELECT Payments.Pupilname, Payments.studentID, Payments.Class, Payments.Term, PaymentsT.Payno, PaymentsT.Itempaidfor, Payments.Amount, Payments.PayCode, PaymentsT.Schoolfees, DSum("Schoolfees","PaymentQ","PayCode ='" & [PayCode] & "'") AS Sumpaid, DSum("Amount","Payments","Class ='" & [Class] & "' AND Term ='" & [Term] & "' AND YearDatemade ='" & [YearDatemade] & "'") AS TermFees
FROM Payments INNER JOIN PaymentsT ON Payments.PaymentID = PaymentsT.PaymentID;

 

[IngressBusiness]

What happens? Is YearDateMade a text or number field? If number, don't use apostrophe delimiters.

Syntax is now correct but the DSum() is not really making sense.

Does Payments table have fields Class, Term, YearDateMade?

What table is the query pulling from? Show the SQL statement.

Please find the Database sample attached

 

[June7]

As I said, don't use apostrophe delimiters for number field. Remove them for YearDatemade criteria.

However, the result of DSum() is a text value, not a number.

Query in db is not showing the SumPaid calculation which won't work anyway because it references the query it is constructed in. Can't do that.

No need to repeat prefixes like "primary" and "term" and "PMD:" and "REG.No:" nor suffix "UGX" in data - just unnecessarily uses up space.

 

[IngressBusiness]

As I said, don't use apostrophe delimiters for number field. Remove them for YearDatemade criteria.

However, the result of DSum() is a text value, not a number.

Query in db is not showing the SumPaid calculation which won't work anyway because it references the query it is constructed in. Can't do that.

No need to repeat words like "primary" and "term" and "PMD:" in data - just unnecessarily uses up space.

Thanks alot Friend, it works
God bless you