Why do I get an Access error #Name? in my form footer?

ES-Components
ES-Components used Ask the Experts™
on
I have an Access query BookingWeeklyTotal with Column names of Total and Expr1

I have a subform called BookingCurrentDay that lists all the details of an order booked on the current day.

In the form footer of the subform I put a text box. I want the text box to add all the orders of the week taken from the
query BookingWeeklyTotal. In the text box I use the Name field = Total and the Control Source I put =BookingWeeklyTotal![Total]

I  get an Access error #Name?  What am I doing wrong?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Open query designer and build reference to your field, then copy result to your field on form
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
In the control source of the textbox in the subform footer, try:

= SUM([Total])

Author

Commented:
I cannot get either of these to work.
 
My subform refers to a Record Source "Bookings-CurrentDay"

I am trying to add to this Subform Footer an unbound text field that refers A different Record Source called BookingWeeklyTotal

Perhaps I cannot do what I am trying to do?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Can you take a screen shot of the form and subform in design view and post it here.

Author

Commented:
I uploaded the entire access file.
Bookings.accdb
Distinguished Expert 2017

Commented:
If you want to reference data that is NOT in the bound recordsource, you will need to use a domain function such as dSum() or dLookup()
As I can see, Pat give you correct answer.
Place
=DLookUp("Total","BookingWeeklyTotal")
as a record source.
I found my own solution by creating another Form based upon the Week Ending Total query which produced the desired results.
I then dragged this Form onto the Daily Booking Form. When you launch the Daily Booking Form you see the desired results which
shows both the Daily Bookings and the Weekly Total.
 Rick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial