Results 1 to 3 of 3
  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Odd Averaging Function Needed

    I have a bunch of fields with values organized by date. The dates to be displayed in a report are chosen by a Start Date and End Date parameter in a query.



    For the most part, subtracting the two dates and adding one (which I still need to test) will be the dividing number for the total for an average. However, there are days when the value for a field can be left blank. I need a function that can subtract one from the average value (provided by the two dates) for every field left blank.

    Here is what I have thought of so far:

    QUERY (parameter):

    Between [Start Date] And [End Date]

    On the REPORT Form:

    Dim aveDivider As Integer

    aveDivider = [Start Date] - [End Date] +1

    Private Function oddAve(ByRef aveDivider As Integer)

    For Each Dates in Dates.DateField
    If Dates = "" Then
    aveDivider = aveDivider -1
    End If
    Loop

    On textbox totals for odd Averaging (where values are blank):

    oddave(aveDivider)

    My VBA is weak, so I'm not sure about something: The right naming conventions for choosing each of my different dates from the Date field in my table/query.

    I will try to follow these instructions to extract the days from the dates and subtract them: http://www.techonthenet.com/access/q...nvert_date.php

    Am I on the right track, or should I do this a different way?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you tested the code? I doubt it will work. VBA manipulates data through recordsets. A recordset is opened by running an SQL statement. See if this offers guidance http://www.everythingaccess.com/tuto...ith-Recordsets
    Last edited by June7; 03-21-2012 at 12:21 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    The code I wrote won't work, but someone was kind enough to help me and provide a code that should. I still need to test it though and see.

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

Similar Threads

  1. Access 2003 Averaging Question
    By swicklund in forum Queries
    Replies: 1
    Last Post: 02-11-2012, 02:31 AM
  2. Help needed in modifying Function
    By Alex Motilal in forum Programming
    Replies: 4
    Last Post: 02-06-2011, 11:59 PM
  3. Averaging Percentages in a form
    By DICKBUTTONS in forum Access
    Replies: 1
    Last Post: 11-18-2010, 01:22 PM
  4. Averaging with a conditional check
    By sarah in forum Queries
    Replies: 1
    Last Post: 10-05-2009, 05:11 AM
  5. Want function to get current function name
    By Davis DeBard in forum Programming
    Replies: 2
    Last Post: 08-13-2009, 05:02 AM

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