By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

P: 122
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:
Expand|Select|Wrap|Line Numbers
  1. ="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:
Expand|Select|Wrap|Line Numbers
  1. Run-time error '2465':
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. OrderNumber as Long,
  2. ContactName as Text,
  3. CompanyName as Text,
  4. SalesDate as Date/Time,
  5. Branch as Text,
  6. ProjectNumber as Integer,
  7. ProjectCost as Double
May 4 '10 #1

✓ answered by Jim Doherty

Expand|Select|Wrap|Line Numbers
  1. ="Current Projects for " & IIf(IsNull([Branch]),[CompanyName],[Branch])

Share this Question
Share on Google+
3 Replies

Jim Doherty
Expert 100+
P: 897
Expand|Select|Wrap|Line Numbers
  1. ="Current Projects for " & IIf(IsNull([Branch]),[CompanyName],[Branch])
May 5 '10 #2

P: 122
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?
May 5 '10 #3

Expert Mod 15k+
P: 31,308
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.
May 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.