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

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Open query designer and build reference to your field, then copy result to your field on form
Dale FyeOwner, Developing Solutions LLCCommented:
In the control source of the textbox in the subform footer, try:

= SUM([Total])
ES-ComponentsAuthor 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?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Dale FyeOwner, Developing Solutions LLCCommented:
Can you take a screen shot of the form and subform in design view and post it here.
ES-ComponentsAuthor Commented:
I uploaded the entire access file.
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.
as a record source.
ES-ComponentsAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.