Report Text Box Control expression problem

[tnmsr2010]

Need some guidance from experts on what could be causing my issue with #Error displaying in text box control on MS Access 2010 report.

I have a bunch of underlying querries this report is built on. These querries use the Nz function to display 0 "zero" instead of Null. I have triple checked and had co-worker double check all spelling referencing query field names, etc.. I am thinking it may have something to do with one user has a dash or hyphen in their last name which is how it is in user table and subsequent querries and crosstabs. When displaying this exact field on report works but it will not when used in expression for text box control in question.

This is causing me to go bald pulling my hair out racking my brain and need help as the users of this site always provide. I am out of ideas and things to check. Thank you in advance.

This expression for the report text box control including user with hyphen in last-name DOES NOT work. Note I have substituted FirstName1.LastName1 instead of real world user names to simplify and to protect real names.

="Invoice Number " & FormatNumber(Sum([qry_CR02ClassifyInvNumErrors].[FName1 LName1]+[qry_CR02ClassifyInvNumErrors].[FName2 L-Name2]+[qry_CR02ClassifyInvNumErrors].[FName3 LName3]),0) & " :"

However, this expression does work. Note I removed the user that has hyphen in their last name, "+[qry_CR02ClassifyInvNumErrors].[FirstName2 Last-Name2]" and it works (i.e no longer displays #Error for the report text box control).

="Invoice Number " & FormatNumber(Sum([qry_CR02ClassifyInvNumErrors].[FName1 LName1]+[qry_CR02ClassifyInvNumErrors].[FName3 LName3]),0) & " :"

 

[tnmsr2010]

Resolved: I changed the report text box control expression to the following and now it works as expected. I am not sure why this resolved it though. Couple things I can think of is I was already displaying each user's Invoice Number Errors (e.g. [qry_CR02ClassifyInvNumErrors].[FName1 LName1]) on report and referencing their named controls eliminated possible conflict. Other thng is eliminiating the SUM function seemed to help.

="Invoice Number " & FormatNumber([txt_FName1LName1InvNumErrors]+[txt_FName2L-Name2InvNumErrors]+[txt_FName3LName3InvNumErrors],0) & " :"

I apologize as I do not know how to mark this thread as [RESOLVED].