Referencing data in the footer section 1

[neillovell]

looking at the properties of the footer section nothing appears under DATA, and when copying a text box to this area I get #Error. Can you not reference data in this section?

I ask because I have a list of projects and their costs, and the grand total, and they are arranged most expensive to least expensive. The finance dept. want to be able to see the top 5 projects and the total cost of EVERYTHING for the year so far on the same page.

So

PROJECT COST
ProjA £4500
ProjB £3000
ProjC £2500
ProjD £200
ProjE £100

Year To Date £85000


I wanted to cheat and put Year To Date in the footer, then create a button that prints just page 1. That way the top 5 are shown as well as the total.

any thoughts?

 

[PaulBricker]

Neil, if I was going to do this I would create a query that just summed the year to date values and then use a DLookUp to put that value in the Page Footer.

Paul

 

[RobPotts]

What wxpresion are you using to calculate the year to date.

Rob!

 

[neillovell]

At the moment, just the sum of each project's costs.

I've created a query to get the total cost, but I can't find any information on DLookUp!

 

[RobPotts]

If the expression is just

=Sum([Name of the cost Field])

I cannot see why it does not work. Have you tried putting the feild in the header, I've got all mine there and they work.

Rob!

 

[neillovell]

Nope, in both the page header and footer it just returns #Error

I just put it into the Header for each field and I get the sum for that field - so

REPORT

1
2
3

total 6 // in report footer

REPORT

1 1
2 2
3 3 // put in field header

It appears to be getting the sum of the fields above it (report header) or 'with' it (in the field header).

surely it should just be referencing the query? Oh hold on I'll go look...

 

[neillovell]

Nope no good. For the control source I can't tell it to get the value in my query I produced just to hold the total cost.

How do I use the expression editor to tell it to display the value in my new query?

 

[RobPotts]

Hi Neil,

Try

=Dlookup("[Name of Field in Query]","[Name of Query]&quot

You need to keep the quates as far as can tell, but I've no idea why.

Hope this helps
Rob!

 

[neillovell]

Excellent! Thanks Rob.