Solved #type appear and sometimes not

[mhakim]

Dears
i have strange thing when i run report on access

sometimes it will give calculated number and every thing is ok

and sometimes it will give #Type!

formula in Control source , Expression Builder

i am Calculating 4 values from Text appear on report like this

=[BS121]+[BS122]+[BS123]+[BS124]

 

[Uncle Gizmo]

Add Nz to each term:-

Nz([field1],0) + Nz([field2],0) + Nz([field3],0). .......

 

[mhakim]

Add Nz to each term:-

Nz([field1],0) + Nz([field2],0) + Nz([field3],0). .......

IF I do like you say it give me #size! instead of #type!

in both cases when i Referesh report it will give Calculated amount ok with no Errors

 

[The_Doc_Man]

In what context is this report being run?

If that is based on an Access database, those field names make me think something is not properly normalized. I don't know that I've ever seen a #size error. On reports, Access would simply truncate the contents of the text box. Is it possible that this is Access linked to a spreadsheet?

If that report is run from VBA in any Excel context, you should remember that Excel computes differently than Access. It is possible (if this is Excel) that your report data source is not being computed in the correct order. In Excel, you can choose to compute one ROW at a time or one COLUMN at a time, because Excel cells are independent entities. It has to "sweep" through all the cells one at a time. Since Excel formulas are capable of making essentially random references, the order of evaluation can make a difference, and it is even possible that the same cell would give two different answers depending on evaluation order. The fact that you get a better result after a Refresh suggests that a re-evaluation occurs and cleans up something.