Access 2000 Subform expression to calculate the inventory balance by subtracting

[George Pollow]

I have a subform containing inventory transactions by date. I want to calculate the inventory balance by subracting the total used from received.

[June7]

What is data structure? Do you have a field for the transaction type (used/received) and a single quantity field? Or do you enter positive/negative values in a quantity field?

[George Pollow]

I have 2 fields from Pubs Transaction form called 12_In and 12_Out I want to subtract the sum of 12_In from 12_Out to determine "Avail".

I have a text box in the footer of the subform to do the calculation and a textbox in the body of the parent form to display "Avail" but nothing displays.

[NTC]

Math will not calc blanks (nulls) - and so be sure there are 0s; if not write an update query and put 0s in wherever there are nulls; and modify the table property to have a default 0 to avoid more nulls in the future.

Beyond that; break it down into simple steps to discover your problem i.e. =Sum([12_In]) should work and then try =Sum([12_Out]) ; those must first work as tests in your footer text box....then merge them together: =Sum([12_In]) - Sum([12_Out])

[June7]

Populating with 0 should not be necessary but probably wouldn't hurt.

Simple arithmetic with Null results in Null: [field1] + [field2]. Can handle possible Null with Nz() function: Nz([field1],0)

Aggregate functions (Sum, Count, Avg, etc) will ignore Nulls and aggregate records that do have values.

What is the expression in the Avail textbox? Needs to be like:

=[subform container name].Form!textboxname

Give the subform container control a name different from the object it holds.

Is the subform in Datasheet view? Try Continuous View and arrange controls to look like Datasheet. Then the subform footer can be displayed with the summary calcs and no need to have textbox on main form.

[George Pollow]

It still does not work.

Here is my expression for the subform footer:

=sum(nz([12_in],0)-nz([12_out],0))

here is the expression for the form:

=[pubs transaction subform].[form]![avail]

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[George Pollow]

Thank you for your assistance. It helped me get on the right track.

I was building an expression for each calc under "Build Event". I noticed that there was noting showing in "Control Source" so I added the espression there.