Access 2007 Reports - can't find field in group header

[gershwyn]

I have a report that summarizes the costs for various projects, based off a query called ProjectCosts (outlined below.) The report is grouped first by companyNumber, then by branch. Branch can be null for companies that have only one location. The problem I am having is that I put a text box in the branch GroupHeader which displays a title for that section of the report. The controlSource for this control is:

="Current Projects for " & Nz([Branch], [CompanyName])

which should display the name of the branch if there is one, and the name of the company if there is not. This works if the branch is not null, but if it is I get an error. I put a function in the onFormat event to figure out what the error is, and it tells me:

Run-time error '2465':

Microsoft Office Access can't find the field 'Branch' referred to in your expression.

...though it has no trouble finding the field called CompanyName. Does this have something to do with what data is available to a report in a specific section (something I know next to nothing about)? Can anyone suggest a way to get around this problem? Thanks for taking the time to help.

Here are the details on the query it is pulling from:

OrderNumber as Long,
ContactName as Text,
CompanyName as Text,
SalesDate as Date/Time,
Branch as Text,
ProjectNumber as Integer,
ProjectCost as Double

[Jim Doherty]

="Current Projects for " & IIf(IsNull([Branch]),[CompanyName],[Branch])

[gershwyn]

Thank you. That was... a lot simpler than I was expecting. That solves my problem, but can anyone explain why I was getting the error I was getting? Isn't the Nz function essentially the same as writing out an IIF IsNull statement?

[NeoPa]

Strange one :S

I would add a function call in your formula, which included both items as parameters, after your Nz() call which uses Debug.Print to display the items passed. This way you can see what is actually available to the Nz() call. I see nothing obvious as to why this would not have worked. I would certainly recommend usage of Nz() over IIf() if it works.