Need to populate a text box with an average calculated by a query

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
139
Office Version
  1. 365
Platform
  1. Windows
I have written a simple query to pull all the files handled for a certain client, and to calculate the number of days the files were open. I would like populate a textbox on a form with an average of those turn-around times. I can calculate the average with a totals query, but when I try to set the data source on the textbox (which I did with the builder), it doesn't work. I get a #Name? error. This was the control source line:
Code:
=[Turn-Around Query New2]![AvgOfTurn-Around]
Is it possible to call the data from the query through the textbox control, or should I do it through the On Current event of the form? Some other way?

If someone could point me in the right direction, I would appreciate it very much.

Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Your form's recordsource is the Totals query? Then just bind the control to the query field using the property sheet setting. If not then other options might be
- use DLookup to "run" the query and return the value. However, if the query could contain more than one record you'll need criteria in the DLookup such that only one record could be returned otherwise you'll get a random record.
- set the control source to a function that will return the calculation
- assuming that the calculation could be ascertained from a table, DSum function with criteria. However, I suspect that this isn't possible otherwise you wouldn't need the Totals query.

AFAIK, you can't 'run' a query by setting a controlsource property to a query, and a query has to run in order to return a value. Just because you can construct an expression using the expression builder doesn't mean it will work.
 
Upvote 0
Create a text box on your form.
Open the properties screen of the text box.
Go to the data tab, and select "Record source".

In this box type: =Dsum("[AvgOfTurn-Around]";"Turn-Around Query New2")
Instead of DSUM, I think DLOOKUP may also work. It's been a while I used this. :)

I am not sure if this will automatically refresh when you change any data. So you might have to include a event handler that updates this field when the source data changes.
 
Upvote 0
Thanks to both of you for your responses. As the needs of this particular project evolved, it made sense to go in a bit of a different direction. Rather than trying to return the values to fields on the form, I simply allowed the queries to run and open to read-only datasheet view.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top