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

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
69
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,129
Office Version
  1. 365
Platform
  1. Windows
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.
 

petertenthije

Active Member
Joined
Sep 25, 2012
Messages
264
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,890
Messages
5,655,816
Members
418,238
Latest member
LizzyG

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
Top