How to fix #size! and #type!

Dear @Chloe Roffe

Thank you so much for contacting Microsoft Q&A Forum. 

Based on your description, the #Size! and #Type! errors in Microsoft Access reports typically stem from issues like type mismatches (e.g., attempting numeric operations on non-numeric or null values), invalid references to subreport controls, or calculations involving subreports with no data. These can occur in expressions using the Nz function for handling nulls, especially during subtraction. Below are troubleshootings and fixes tailored to your formula =Nz([asep])-Nz((Reports![Rep16_loc_reg]!Report12csub!col1)), assuming [asep] is a numeric field/control and col1 is a numeric control on the subreport. 

Your reference lacks the .Report property, which is required to access the underlying report object within the subreport control. Without it, Access may treat the reference as invalid, leading to #Type! (type mismatch) or #Size! (invalid operation/size issue). 

Force Numeric Output with Nz(..., 0): 

The Nz function without a second argument returns a zero-length string ("") if the value is null. Subtracting a string from a number causes a type mismatch (#Type!). Specifying 0 ensures a numeric result. 

• Updated formula: 

=Nz([asep], 0) - Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0)

• If [asep] or col1 could be non-numeric (e.g., text formatted as numbers), wrap them in Val() or CDbl() to convert: 

=Val(Nz([asep], 0)) - Val(Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0))

 Handle Cases Where the Subreport Has No Data: 

If the subreport (Report12csub) returns no records, referencing col1 can cause #Size! (due to invalid aggregation or size issues in the calculation) or #Type!. Use the HasData property to check for records and default to 0 if empty. 

• Updated formula: 

=Nz([asep], 0) - IIf(Reports![Rep16_loc_reg]!Report12csub.Report.HasData, Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0), 0) 

• This evaluates as: If the subreport has data, use col1 (with Nz for nulls); otherwise, subtract 0. 

Trap General Errors with IsError(): 

To catch any remaining #Size! or #Type! (e.g., from edge cases like division by zero implicitly or bad data), wrap the entire expression in IsError(). 

• Updated formula: 

=IIf(IsError(Nz([asep], 0) - IIf(Reports![Rep16_loc_reg]!Report12csub.Report.HasData, Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0), 0)), 0, Nz([asep], 0) - IIf(Reports![Rep16_loc_reg]!Report12csub.Report.HasData, Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0), 0))

For the final version of the formula: 

=IIf(IsError(Nz([asep], 0) - IIf(Reports![Rep16_loc_reg]!Report12csub.Report.HasData, Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0), 0)), 0, Nz([asep], 0) - IIf(Reports![Rep16_loc_reg]!Report12csub.Report.HasData, Nz(Reports![Rep16_loc_reg]!Report12csub.Report!col1, 0), 0))

I hope this information can help you and if it cannot be fixed, please kindly send me more details of your error message by screenshots so that I can research more for you. 

Wish you a pleasant day! 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.