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.DateFieldIf 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?