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

[mikenelena]

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:

=[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!

 

[Micron]

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]

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.

 

[mikenelena]

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.