Expression with average

[gint32]

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
=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:


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

any help appreciated!

[PhilS]

You should take a look at the domain aggregate functions, particularly at DAVG (https://support.office.com/en-us/article/davg-function-c7270c5d-e3b9-4f26-9615-2133a277066d).

[gint32]

You should take a look at the domain aggregate functions, particularly at DAVG (https://support.office.com/en-us/article/davg-function-c7270c5d-e3b9-4f26-9615-2133a277066d).

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


=avg(((Workers.OT_Hours)>0) AND ((Workers.workLocation)="Texas"))
to as follows:


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

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


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

Many thanks for responding though!

[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")

[gint32]

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