sum of a column =< a field from a form.

[wshs]

im using a query and thought this would be the simplest way.

under ProjectBudgetAmt
criter: =<[forms]![frm_projectbud]![projectamt]

seems like it's not showing any reaction?!?

 

[Golom]

The title of your post sum of a column =< a field from a form doesn't quite match what your code looks like it's doing.

The code appears to be restricting the field ProjectBudgetAmt to values less than a form field. The title however refers to the SUM of a column. Can you clarify what you are trying to do ... and possibly post the complete SQL?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[wshs]

well on a form "projectBud", there's a field called "projectamt"

This form contains a subform(datasheet) where user can create sub categories. I'm just trying to make it so that amounts(sum of) in subform (projectbudgetamt) doesnt exceed "projectamt"

 

[Golom]

Your SQL probably needs a HAVING clause of the form

HAVING SUM(Amounts) < [forms]![frm_projectbud]![projectamt]

but we would need to see the SQL to be sure.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[wshs]

here is the sql..

SELECT tbl_ProjectBudget.pkeyProjBudID, tbl_ProjectBudget.ProjectBudName, tbl_ProjectBudget.ProjBudAmt, tbl_ProjectBudget.EncAmt, tbl_ProjectBudget.EncPaid, CCur(tbl_projectbudget.encamt-[encpaid]) AS EncBal, CCur(tbl_projectbudget.projbudamt-[encbal]) AS PbBalance, tbl_ProjectBudget.fkeyProjectID
FROM tbl_ProjectBudget INNER JOIN tbl_ProjectMain ON tbl_ProjectBudget.fkeyProjectID = tbl_ProjectMain.PkeyProjectID
WHERE (((tbl_ProjectBudget.ProjBudAmt)<[forms]![frm_projectbud]![projectamt]));