Calculated columns showing #error??

[bubu678]

I am using Access 2003. I have a form that shows many calculated columms (some using =Dlookup(), other using =ufMyFunctions() stored in Modules). I update only 2 columns and the the form is set up as a ContinuousForm.

No problem, everything displays when I open it. Also in the form is a call (found in Modules) to function that updates a column on that table. Basiclly when opening the form it could do some updates. As I said everything works great when I open the form.

What I have noticed is that when I open the form using the following:
stDocName = "frmIPMVPCosts"
DoCmd.OpenForm stDocName, acNormal
DoCmd.GoToRecord , , acFirst
DoCmd.GoToRecord , , acLast
DoCmd.Close acForm, "frmIPMVPCosts", acSaveYes

all the columns display #ERROR, except the bound columns, which display correctly. The reason I do this method is on a change of value for some of the columns used in the calculated columns on the form, I need to force an update automatically. Instead of waiting on the user to open and close the form, I do it on a CLOSE event in another form that updated some columns used in the calculated columns. That way the reports can up to date also.

This is a single user system for now. Any ideas why the calculated columns are not visible? Is there some setting I have to do? I use this method on other forms that have no calculated columns and it all works.

Thanking you in advance for any help.

Cheers, Boris

 

[Sum Guy]

This is a common problem (for me at any rate).
It often occurs when you atempt to multiply zero times zero or divide zero into zero. Access can't do this with zeros.

Try the IIF function with the calculations:

IIF ([Field1]>0 and [Field2]>0 ([Field1]*[Field2])),0

Haven't done this for a while so my coding may be off.

 

[boblarson]

Actually Nulls, not zeros will usually cause the problem. Zeros will cause a problem if you are DIVIDING by them (which of course you can't do) but adding, multiplying or subtracting with zeros won't be a problem.

Now, NULL values on the other hand WILL cause a problem in any mathematical operation. So you need to account for those by using the NZ function to tell it to use something else if it is null. So, for example:

=(NZ([YourField1],0) + NZ([YourField2,0))/Nz([YourField3],1)

 

[bubu678]

Thank you everybody for your replies. I have that type of logic in most of the code. My point was that the form displayed OK in one method, but then when opend under a similar method the same form (nothing chged internally as to database, sort or filtering) did not display the same. What I ended up doing is putting all the logic into a query. Doing that way, I ahd to rework some more of the logic, such that I didn't neet to open the form in the same method (as posted) any more. Hence no more problem and don't have to investigate further.

Thank you all for your help.