Calculated control errors

[linger]

hi,

i've been trying to trouble shoot this expression in my form. I build this expression in a calculated control. The expression calculates the average of fields being entered. For example if 1 field is entered then the value is divided by 1. but if 2 fields are entered, then the two values are added and divded by 2.

field3: =(nz([field1])+nz([field2]))/((IsNull([field1])+1)+(IsNull([[Field2])+1))

i am trying to do a calculation in the control and then save it in the new field of the form but the expression is returning an error (#Name?) I have the .dll file but somehow it's not letting me use any of the build in functions.

Do I need to use a query or can I just do the whole calculation in the form. I'm not too familiar with using queries.

yiling

 

[bat17]

How are you trying to save it?
Normally you do not save calculated fields but recreate them each time that you need them, this saves on unnecessary storage and means that if a value changes the calculation is automatically updated.

HTH

Peter

 

[linger]

i haven't tried saving it. the first problem is...Access doesn't even recognize nz or IsNull when i type it into the control field.

Secondly, my goal is to be able to save the calculated output. i need to be able to calculate the expression on the form as and when the user is inputting the data and save it straight to the table without having to click on any specific button. it will also be able to erase the old value and update the new value and save again if the user changes the variables affecting the calculation. i hope i didn't confuse you yet.

thanks
yiling

 

[bat17]

It looks as if Access does not accept the logic of IsNull([field1]) and that you will have to do it the long winded way =IIf(IsNull([field1]),0,1)

HTH

Peter

 

[klb]

If you remove one of the two squared left brackets in your last IsNull, you should be okay

Sometimes it is as simple as a typo.

 

[linger]

thanks everyone,

i typed the expression in error in the previous msg. it was not in any of my expression. i isolated the problem. the other fields were not being updated so it screwed up my field calculations and gave me an error.

sorry!

for the other question, how do i update the new control field with the calculated expression in the other control field so that it can be saved into the records. will i be doing it right if i just went into the properties box and type in the On Change box currentValue:=calculatedValue ?

yiling