Results 1 to 4 of 4
  1. #1
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694

    EAvg() Function


    By default, DAvg does not take into account NULL values. If you run it on a field that contains NULLS, it skews the result. This function solves that issue:

    Code:
    Function EAvg(strFld As String, strTbl As String, Optional strCriteria As String)
    
    Dim lngRecs As Long 'TOTAL RECORDS WITH NULLS
    Dim lngSum As Long 'SUM OF THE FIELD VALUES
    
       lngRecs = DCount("*", strTbl)
       
       lngSum = DSum(strFld, strTbl)
    
    EAvg = lngSum / lngRecs
    
    End Function
    Last edited by ajetrumpet; 09-10-2010 at 07:35 AM.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    EAvg = lngSum / (lngRecs + lngNulls)
    End Function
    why do you divided by (lngRecs + lngNulls)?
    lngRecs is total records already..
    Isn't it enough like this?
    Dsum(strfield,strtbl)/dcount("*", strtbl)

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    thank you for pointing that out! wow ... can't believe I didn't notice it! NOW it will work correctly. I have edited the post. thanks!

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    thank you for giving lots of helpful functions to us.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. if function
    By lolo in forum Queries
    Replies: 1
    Last Post: 08-01-2010, 11:38 PM
  2. Function Help
    By th3spankst3r in forum Programming
    Replies: 12
    Last Post: 03-22-2010, 12:41 PM
  3. Want function to get current function name
    By Davis DeBard in forum Programming
    Replies: 2
    Last Post: 08-13-2009, 05:02 AM
  4. function key
    By marianne in forum Access
    Replies: 5
    Last Post: 05-14-2009, 01:26 AM
  5. Avg Function
    By hiker8117 in forum Access
    Replies: 3
    Last Post: 04-23-2009, 11:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums