Expression with average

Hi Everyone,

I am struggling with getting an average from tbl_Something.Specific_columns (=numbers) to display the results within a textbox when the form is loaded, my form already retrieves other data based on other boxs and comboboxs etc, which I don't want to interfere with.

So I attempted to insert the following into the control of a textbox

Code:

---

=avg(((Workers.OT_Hours)>0) AND ((Workers.workLocation)="Texas"))

---

But access is having trouble with evaluating the above formula and gives an error
Can anyone help me on achieving the result I wish.

I built a query using the wizard and it works just fine as below:

Code:

---

SELECT Avg([OT_hours]) AS AverageOT_Hours
FROM workers
WHERE (((workers.OT_Hours)>0) AND ((Workers.workLocation)="Texas"));

---

any help appreciated!

You should take a look at the domain aggregate functions, particularly at DAVG.

Quote:

---

Originally Posted by PhilS

You should take a look at the domain aggregate functions, particularly at DAVG.

---

Thanks for the suggestion but your suggestion never made much difference, I amended the following

Code:

---

=avg(((Workers.OT_Hours)>0) AND ((Workers.workLocation)="Texas"))

---

to as follows:

Code:

---

=DAvg(("[OT_Hours]"),"Workers",("[Worklocation] = 'Texas'" and "[OT_Hours]>0")

---

As it doesn;'t give me the same average number that I get from ..

Code:

---

SELECT Avg([OT_hours]) AS AverageOT_Hours
FROM workers
WHERE (((workers.OT_Hours)>0) AND ((Workers.workLocation)="Texas"));

---

Many thanks for responding though!

This is doing a string comparison which equates to FALSE

"[Worklocation] = 'Texas'" And "[OT_Hours]>0"


Should be...

=DAvg("OT_Hours", "Workers", "Worklocation = 'Texas' And OT_Hours > 0")

Or dlookup from your working query...


=dlookup("AverageOT_Hours", "YourWorkingQuery")

Quote:

---

Originally Posted by jonh

This is doing a string comparison which equates to FALSE

"[Worklocation] = 'Texas'" And "[OT_Hours]>0"


Should be...

=DAvg("OT_Hours", "Workers", "Worklocation = 'Texas' And OT_Hours > 0")

Or dlookup from your working query...


=dlookup("AverageOT_Hours", "YourWorkingQuery")

---

Thanks you for both suggestions(both work), not sure which runs faster though but either way I am happy I get the system to work, again many thanks for your help