Odd Averaging Function Needed (1 Viewer)

[Heatshiver]

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/questions/convert_date.php

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

 

[NickHa]

Is there any reason you can't exclude the records with empty fields from the query? If it's a single field you are counting, then a WHERE clause in the query would do it - but if multiple fields per record, where some have values and others don't, then VBA maybe the only solution. I'm assuming [Start Date] and [End Date] are not the fields you are checking for empty values?

If you provide more detail about the fields involved, we can help. Be specific about the number of fields involved and how their values vary between records.

The VBA isn't right for what you need, but the principle is there. Avoid using names like Dates as a general rule, and don't declare the same variable names inside and outside of procedures and functions (as in aveDivider), unless you really know what you're doing. Scope problems are nearly always hard to find and debug.

 

[Heatshiver]

Thanks for the reply.

There are multiple fields where on one day a value may be present, and on others, there may not.

I'll try to give a better example of what is meant to happen:

a) We have three columns (fields) of data (values). There is Date, Black iPod, and White iPod.

b) Date always has a value and is connected to Black iPod and White iPod.

c) We average out not according to date, but to how many dates we had iPods sold.

d) There are three dates from 3/18/2012 to 3/20/2012.

e) Black iPod - Sold 20 on 3/18/2012; Sold 0 on 3/19/2012; Sold 10 on 3/20/2012

White iPod - Sold 0 on 3/18/2012; Sold 0 on 3/19/2012; Sold 15 on 3/20/02012

"0" can be considered the blank field as nothing will be put down. This also signifies the days where inventory was sold out.

f) The average Black iPod's sold is 15 (30 total sold divided by 2 days). The average White iPod sold is 15 (15 total sold divided by 1 day).

I hope this is a better explanation of what I am trying to do. I figured converting the date into a number and then subtracting the amount of blank fields (or zeros) would be the best way to get the division number needed.

Any idea how I can set this up in VBA? Please help!

 

[NickHa]

Something like this should do what you want. I wrote this for a dummy table named tSales with fields stDate, stBlack and stWhite to represent the structure you described, so you must substitute your table and field names.

Public Function calculateAverage(ByVal pStartDate As Date, ByVal pEndDate As Date, ByVal pFieldName As String) As Single
calculateAverage = DAvg("[" & pFieldName & "]", "tSales", "stDate Between #" & Format(pStartDate, "mm/dd/yyyy") & "# And #" & Format(pEndDate, "mm/dd/yyyy") & "# AND NZ([" & pFieldName & "],0)>0") 
End Function

You can put this wherever convenient, then call the function for each field you want to average.

Here's the test I did:

Private Sub test()
Debug.Print calculateAverage(DateSerial(2012, 3, 18), DateSerial(2012, 3, 20), "stBlack")
Debug.Print calculateAverage(DateSerial(2012, 3, 18), DateSerial(2012, 3, 20), "stWhite")
End Sub

and the results were 15 for both fields, per your example data. Again, use your source date fields in place of the DateSerial functions (must be as Date fields) and the field name you want.

 

[Heatshiver]

@NickHa - Sorry didn't get back to you sooner, had to go on a short business trip.

I tried the code, but I'm getting an error, which I believe may be from either the way I have the dates when calling the function, or how the text box is meant to display the number:

1) I created a dummy database with the exact names that you used for the tables and fields. As well as the same amounts and same dates.

2) I placed your Public Function into a module called "Odd Average".

3) I created a text box on a report.

4) I placed the line of code to call calculateAverage into the Click event of the text box. (Need a better way to have this displayed, was thinking somehow with Form Load event.)

*5) I then replaced the DateSerial functions with the created date parameters for my query that allows a range of dates to be chosen:

Debug.Print calculateAverage([Start Date], [End Date], "stBlack")

I think this is where it goes wrong. I get a run time error, "2465", when I click the text box.

Even if I use the code as you have it (e.g. DateSerials), when I click the text box nothing happens...

How do I properly refer to these two variables inputted by the user? Or is there another issue occurring?

 

[NickHa]

I have amended your example report as follows:

1. Changed the Record Source to hold the query directly, rather than pointing to the external query. There is nothing wrong with the way you did it, but my aim was to make the report entirely self-contained.

2. I moved the function from your module and put it in the Report's own module (again to be self-contained). I changed it to Private because it is only used in the report.

3. I deleted your text box (Text24) from the report body and added two text boxes in the report footer - one for black units and one for white units. In these, I used the function as their record source, with the appropriate constraints.

I also tidied up the layout so that the result would be more obvious.

I deleted the query and module, to ensure I had excluded all references to them.

Lastly, I changed the data table to contain values which would not just yield 15 as the result, so I was sure the output was correct within requirements.

I think this is what you wanted, but I may have misunderstood along the way. So any questions, please ask.

 

[Heatshiver]

Perfect! You are a life saver!

 

[NickHa]

You are very welcome.