Calculations not displaying

[Keiath]

Sorry I have this in the forms section but no one seems to look there so trying my luck here

I am having a problem in my form,

I have a stock control that has tabs, on the tab (Activity) I have a subform that is populated by a table called Stock Activity and simply shows all the stock in and stock out.

In the footer I created a text box (text17) have this sum

=Sum([StockIn]-[StockOut]) to calculate the actual stock

On the First tab (Stock Data) I have a field called Total Stock and in that I have this:-

=[Stock Activity subform2].[Form]![Text17]

But it doesn't show the results. But if I change the =Sum([StockIn]-[StockOut]) to just =Sum([StockIn]) or visa-a-versa it displays the number

Why is this happening? Any ideas?

 

[JANR]

=[StockIn]-[StockOut]

In other word lose the Sum()

JR

 

[Keiath]

That doesnt work comes up with a 'The expression you entered contains invald syntax.

And I tried it the other way losing the [ ] still dont work

 

[JANR]

I think you have a structural problem with the table that your subform is based on, does the table look something like this:

ItemID
StockIn
StockOut
..other fields

If so then thats not how it suppose be, any transaction table should have In/Out in one filed with StockOut be a negative number so when you do:

=Sum([StockInOut])

you get the current stock.

I suppose you can create 3 controls in you subforms footer where the Controlsource be something like this:

=Sum([StockIn]) <--- First control
=Sum([StockOut]) <--Second Control
=[FirstControl]-[SecondControl] <---- Point to this control on you main form

JR

 

[Keiath]

Got it working perfectly using

=Nz(Sum([StockIn]),0)-Nz(Sum([StockOut]),0)

It was to do with Null values in records that was causing it to not display

 

[JANR]

Ahh those Nulls.

Glad you got it working

JR