Consulting

Results 1 to 5 of 5

Thread: Expression with average

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    42
    Location

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

  2. #2
    You should take a look at the domain aggregate functions, particularly at DAVG.
    Learn VBA from the ground up with my VBA Online Courses.

  3. #3
    VBAX Regular
    Joined
    Jan 2015
    Posts
    42
    Location
    Quote Originally Posted by PhilS View Post
    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

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

  4. #4
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    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")

  5. #5
    VBAX Regular
    Joined
    Jan 2015
    Posts
    42
    Location
    Quote Originally Posted by jonh View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •