Automatically calculated value - how to put in a report?

[SteveC24]

Hello

My fish database is almost complete now, but this glitch has been bugging me from day 1.

I have a main fish form "frm Fish enter"
That form has 2 subforms, "frm Purchases enter" and "frm Deaths enter"

In the main form, I have a field called "housed", which calculates how many fish the user has of this particular species, by adding taking the total number of deaths from the total number of purchases.

This is done using this formula/expression or whatever you call it:

=Nz([Purchases].Form!purchasestotal,0)-Nz([frm Deaths].Form!deathstotal,0)

That works great, and I am very happy with it, except one thing...

I have a report which lists all the fish the user has...but I cannot put how many fish there are, as the automatically calculated value I was talking about isn't saved to a table.

Is there are way to make the calculated value save itself to a table (tbl fish) each time the form is closed or something? Or is there a better way round this problem?

Thanks very much for any help you can offer.

 

[Waltang]

It sounds like your report just needs a running sum field.

 

[SteveC24]

How might I go about making that work? The fields I think you probably need (the quantities of fish bought and died) are not in the report.

 

[Waltang]

Can you add them to the report and make them not visible?

 

[neileg]

...or add them to the query on which your report is based and refernce these in the report.

 

[Rich]

Just add the fields to the reports underlying query, then it's just Sum([Sold]-[Died])

 

[SteveC24]

OK, with a bit of fiddling, I have managed to get it to work to a certain extent!

I now have a field on the report with this control source:

=Sum(Nz([tbl Purchases.Quantity],0))-Sum(Nz([tbl Deaths.Quantity],0))

That is roughly the same code as is in the form, except with the additions of SUM.

It returns some weird values though, that I cannot figure out for the life of me!


An Example:

4 Purchases & 7 Purchases
2 Deaths

Should return 9 [(4+7)-2]. It returns 9 in the form...that is fine.

In the report...it returns 7??? It all works OK until you add a death, then the numbers go weird?!?

Help!