Simple Subform Calculation

[sondriven]

I have a subform with these two fields:

1. UnitsReceived
2. UnitsSold

I want to keep an running sum of what I have on hand.

I created a text box and put it in the footer of the subform with this control:

=Sum([UnitsReceived])-([UnitsSold])

Its staying blank. Am I suppost to add anything else to this?


I tried a search for this but came up short.

Thanks.

john

 

[Sohaila Taravati]

I always do my calculations in my query. Do this in your query:
Units on hand:Sum([UnitsReceived])-([UnitsSold]), then put the field Units on hand at the footer.

 

[sondriven]

In my query Im trying to put this:

Field: UnitsOnHand: Sum([UnitsReceived])-([UnitsSold])

And I keep getting errors.

#Error

Any other ideas?

Thanks for the reply.

 

[Jerry Stoner]

try this in form not query

=Sum([UnitsReceived]-[UnitsSold])

 

[sondriven]

I tried this for the control, in the text box, in the form, in the footer:

=Sum([UnitsReceived]-[UnitsSold])

and Im still getting this message "#Error"


Stumped...


The only thing I can think of is that, in my table for the subform, does the properties for these have to be any thing special?

Long Integer and such?

 

[Jerry Stoner]

Is UnitRecieved and UnitsSold the name of the text box in the form or the control source? You need to use the control source not the name. If thats not it I dont know. I did test this to make sure it works and it does.

 

[sondriven]

Man, how frustrating!

I double checked and I forgot a space inbetween the words, but it didnt help.

The #Error is gone, but now its just blank.

 

[Jerry Stoner]

Ok try this

=Sum(Nz([UnitsReceived],0)-Nz([UnitsSold],0))

 

[sondriven]

Dude!! It works! Just so I know can you explain that formula a little.


Thanks again!

John

 

[Jerry Stoner]

Nz function

Nz converts Null into whatever you define in the expression ie. if UnitRecieved is Null it returns 0. For a complete explanation look up Nz in access help.

 

[sondriven]

Hi Jerry,

I split the database so I can run the program on our network. I put the Front End on the computers in the office and the backend is on the server. They are linked to the same *.be file. For the "UnitsReceived, UnitsSold" formula, it is only working on my computer. Everyone else is receiving a #Error in that field.

I double checked which front end we have and its all the same. Do you know what would cause this?

Thanks

John

 

[Rich]

I would guess it's a missing reference problem, there have been numerous posts here on the subject

 

[sondriven]

I did a search in my Access Help and it pointed me in the direction of Tools->References

I have Access2K and am missing this option.

I did a search in the Access Forum and couldnt find my problem. Any help on this would be appreciated. Thanks.

John

 

[Jerry Stoner]

Missing option

I have Access2K and am missing this option

You must have this "option". Go to design view of any form and click on the code button (2 to the right of the toolbox). Now click on tools then references and look for "Missing" and uncheck those items. That should do it.

 

[sondriven]

Okay in my References only these are checked:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library


All the others are unchecked. Is this okay or is this why Im having the problem with the other computers getting the #Error.

My computer works perfect, but each time I try to put the FE on another computer, all works but this one formula. Everyone updates fine too.

John

 

[Jerry Stoner]

references

Try checking Microsoft DAO 3.6 Object Library.