project variance on form

[kev.thorpe]

Hi..

I'm hoping someone out there might be able to help..

I am creating a form so that our planners can view their construction
project financial status. When a project is initiated it is entered
into access with all the pertinent details on a tabbed form. The first
tab contains general info - the second tab (a subform) contains their
project budget. Their project budget is broken down into several
categories such as construction, engineer, architect, contingency etc.

As payments are made against projects they are charged to the project
when the invoice is entered in access. When each invoice is entered
the amount is charged against a general ledger account. Basic invoice
information such as vendor, date etc is kept in a table, but the
calculations are stored in a query.

What I am attempting to do is create a form that will display their
project budget (broken down by category), the amount spent in that
category (from the posted invoices)and the variance. EAch general
ledger account has a corresponding category on the project budget (in
some cases two GL's will lead into one category)

I created a form that has all the project budget information, and
created a subform based on a query that totals each GL by project. My
intent was to somehow draw the information from the query into unbound
text boxes and do a simple calculated control for the variance.

Th problems I am running into is that a project does not necessarily
have costs associated to it in all categories at all times and my
query does not show "0" for those GL's that have nothing posted
against them. I created a crosstab query that was great but am unable
to utilize it on a form.

My second problem is actually pulling the individual information from
the subform and placing it on the form.

Any help from anyone who has attempted something similar is greatly
appreciated.

Kevin

 

[Guest]

Do you use the NZ([FieldName]) function in your query or report?
If there are Null values this function translates them to 0 (Zero)
Without it, Access behaves strangely when it encounters a null value.

 

[kev.thorpe]

Do you use the NZ([FieldName]) function in your query or report?
If there are Null values this function translates them to 0 (Zero)
Without it, Access behaves strangely when it encounters a null value.

Hi..

I'm hoping someone out there might be able to help..

I am creating a form so that our planners can view their construction
project financial status. When a project is initiated it is entered
into access with all the pertinent details on a tabbed form. The first
tab contains general info - the second tab (a subform) contains their
project budget. Their project budget is broken down into several
categories such as construction, engineer, architect, contingency etc.

As payments are made against projects they are charged to the project
when the invoice is entered in access. When each invoice is entered
the amount is charged against a general ledger account. Basic invoice
information such as vendor, date etc is kept in a table, but the
calculations are stored in a query.

What I am attempting to do is create a form that will display their
project budget (broken down by category), the amount spent in that
category (from the posted invoices)and the variance. EAch general
ledger account has a corresponding category on the project budget (in
some cases two GL's will lead into one category)

I created a form that has all the project budget information, and
created a subform based on a query that totals each GL by project. My
intent was to somehow draw the information from the query into unbound
text boxes and do a simple calculated control for the variance.

Th problems I am running into is that a project does not necessarily
have costs associated to it in all categories at all times and my
query does not show "0" for those GL's that have nothing posted
against them. I created a crosstab query that was great but am unable
to utilize it on a form.

My second problem is actually pulling the individual information from
the subform and placing it on the form.

Any help from anyone who has attempted something similar is greatly
appreciated.

Kevin- Hide quoted text -

- Show quoted text -

I did try using the nz function, it doesn't work. All it does is
replicate the records I already have containing values. I think the
problem is that my gl table has about 20 gl codes, but a project
rarely generates invoicing for all gl's. Maybe I am using the nz
function wrong. My query has the fields Project ID, Total, and GL#.
This query is based on a query containing calculations (to get the
Total) and the table that holds my GL info GLID, GL# and GL Name. I
tried using the expression nz([Total],"0").

I am not overly proficient in access by the way.