#Error from query field

[handlebarry]

hi - I have a subform (subform A) with a query as a datasource. One of the query fields is a "calculation":

Days Late: IIf([Day1]<=14,"On Time",([Day1]-14)) & IIf([ContractStatus]="NOE PREPARATION","NOE")

Users can jump into another form by double clicking on a unrelated field. However when the user closes this form an error appears on subform A in the field based on Days late -"#Error"

I've tried refreshing, requerying and even recalc-ing the field but this does not work.

Can someone offer some advice please.

 

[Golom]

You are missing the "False" clause in the second IIF. You may need something like

IIf([ContractStatus]="NOE PREPARATION","NOE","")

I would also suggest explicitly converting the computed number of days to a string with Format([Day1]-14,"0")

 

[handlebarry]

hi golom have made the changes you suggested:

Days Late: IIf([Day1]<=14,"On Time",Format([Day1]-14,"0")) & IIf([ContractStatus]="NOE PREPARATION","NOE","")

However this has not solved the problem. Maybe if I explain a little further.

The form concerned has 5 pages on it, each of the five pages contain a subform. On opening the form all fields on all subforms work fine (including the problem field before and after your suggestions).

Each of the 5 subforms have a field called Contract Name. Double clicking any of these contract name fields opens a contract notes form. It is when closing this contract notes form that the error appears in the problem field.

(The error does not occur when looking at the contract notes formusing the subform with the problem field)

Maybe I could try something under the forms/subforms got focus?

thanks for the help