Type! error and refreshing (1 Viewer)

[Caveman]

A weird thing is happening. There is this continuous form.
It has a calculated field with the value: =Nz([OrderAmount]-[AmountReturned],0)
Some records display a #Type! error. When refreshed (F5), the correct value is displayed, but this time other records display this error. It seems completely random. I don't think there really is a type mismatch in the database, because a simple refresh wouldn't fix that.

-it worked fine for 5 years in Access 2010, since we are using 2016 it started.
-it doesn't only happen with a huge amount of records, also when only 15 records are displayed.

Do you know of any difference between Access 2010 and 2016 that might cause this? Someone experienced this too? All help would be welcome!

 

[jdraw]

Review the data in the fields in the record where the !type error occurs.
Look for some inconsistency in the base records.

 

[Mike Krailo]

What if you changed the calculation to:

=Nz([OrderAmount])-Nz([AmountReturned])

 

[MajP]

You may need to wrap each value separately
=nz([OrderAmount],0) - nz([AmountReturned],0)

 

[Caveman]

You may need to wrap each value separately
=nz([OrderAmount],0) - nz([AmountReturned],0)

Actually, that changed one thing: the error changed from #Type! to #Size!
For the rest it happens the same way; just random records, F5-ing the screen solves it, but then a couple of other records display this error. No matter how many times F5 is pressed, there are always 2 or 3 records displaying this. The weird thing is that this never happened with Access 2010.

Is it possible the calculations to be made are too intensive? I mean, it's even a miracle Access displays so many data within a second of opening the form. Maybe some records are not calculated in time?

OrderAmount is a field from the form record source
AmountReturned looks like this: =Nz(DLookUp("StoreamountSum","queryOrdersReturned","OrderID = Form!OrderID"),0)

 

[Minty]

A DLookup will be very inefficient, can you not join that query to your Forms recordsource and also force the result to 0 in that query?

 

[Caveman]

Think I know what you're saying. I am going to try that