#Error in calculated field (sometimes)

[LynnEsther]

I have a continuous form with two, simple calculated fields (=[field1] + [field2]). Roughly 90% of the time when the form is opened, the calculated fields in the first record display #Error. All of the other records on the form are fine. If I hit F9 on the keyboard to refresh, the #Error goes away and the correct field value displays. I have tried requerying the form OnOpen, but that doesn't correct the problem. Is there anything else I might try? Thanks in advance.

 

[boblarson]

Maybe try using the NZ function to wrap the fields in

=Nz([Field1],0)+Nz([Field2],0)

or in the on load event of the form put Me.yourTextBoxnameHere.Requery

to kick it into action. Just a few suggestions.

 

[LynnEsther]

Thank you for your response, boblarson. I tried each of your suggestions and alas, I still get the pesky #Error roughly 90% of the time. I wonder if it is perhaps a timing issue of some sort? It's not a matter of life/death, but I would love to solve this one for my customer. Frustrating...

 

[boblarson]

Can you post the db here? Or, perhaps email it if, after following these instructions:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587
it is too large to post.

 

[georgedwilkinson]

You might also consider wrapping the 2 calculated fields in nz().

Also, if the fields are text, you might want to use:
=Nz([Field1],"")+Nz([Field2],"")
or
=Nz([Field1],'')+Nz([Field2],'')

as potential modifications of what Bob recommended.

Are both fields numeric or are they sometimes text?

 

[LynnEsther]

The nz didn't work. Nor did requerying the individual fields. Sadness. Unfortunately I cannot post the database because it is on a "private" network and there is no way for me to move it from there (not allowed) to the internet. Thanks to both of you who responded. I may simply need to update the user guide to include hitting the F9 key to refresh if they experience the #Error.