K
kev.thorpe
I've posted this before in the forms group but I haven't been able to
get help so I'm trying again.
I have a form designed to display/track construction project budget
and actuals to date.
The main form is based on a query called 'queryproject1553'. The
query is based on two tables ('tblprojectinfo' and
'tble1553toactual'). The main form contains basic project info such
as project ID, project # and project name, along with the project
budget broken down into categories(is construction, mechanical,
electrical etc).
The subform is based on a query "qryprojecttotalbygl', which is based
on 'tblglaccount', 'qryinvoicetotal' and 'tblglaccount'. This query
totals all amounts posted to a project (through invoice entry) by GL.
For any given project the query produces a GL# and a total amount of
that GL posted against the project. If there have been no costs
associated with a particular GL, nothing shows up in the query.
What I want to do is compare the budget amounts (which are displayed
on the main form) to the corresponding GL total (on the subform). I
have tried various iif and dlook up expressions to no avail. I want
to place an unbound text box next to each category on the main form to
pull the gl total information from the subform, and if there are no
invoices posted against a particular gl to return a value of 0.
My current expression is
=IIf(sub1553toActual.Form![GL#]=8106041,sub1553toActual.Form!
SumOfTotal,"0")
For each category I changed the GL# = to the corresponding GL. What
it is doing is only looking at the first record in the subform and
returning the correct value for it, but assigning all others a 0.
Any help is greatly appreciated.
Thanks
get help so I'm trying again.
I have a form designed to display/track construction project budget
and actuals to date.
The main form is based on a query called 'queryproject1553'. The
query is based on two tables ('tblprojectinfo' and
'tble1553toactual'). The main form contains basic project info such
as project ID, project # and project name, along with the project
budget broken down into categories(is construction, mechanical,
electrical etc).
The subform is based on a query "qryprojecttotalbygl', which is based
on 'tblglaccount', 'qryinvoicetotal' and 'tblglaccount'. This query
totals all amounts posted to a project (through invoice entry) by GL.
For any given project the query produces a GL# and a total amount of
that GL posted against the project. If there have been no costs
associated with a particular GL, nothing shows up in the query.
What I want to do is compare the budget amounts (which are displayed
on the main form) to the corresponding GL total (on the subform). I
have tried various iif and dlook up expressions to no avail. I want
to place an unbound text box next to each category on the main form to
pull the gl total information from the subform, and if there are no
invoices posted against a particular gl to return a value of 0.
My current expression is
=IIf(sub1553toActual.Form![GL#]=8106041,sub1553toActual.Form!
SumOfTotal,"0")
For each category I changed the GL# = to the corresponding GL. What
it is doing is only looking at the first record in the subform and
returning the correct value for it, but assigning all others a 0.
Any help is greatly appreciated.
Thanks