Help with a function

[Skip Bisconer]

I want to make a function similar to this one provided to me from PBaldy and I don't know how to make it work for me. Originally when working with Paul I had queries built for each month of the year and this would run what ever query that matched the month the process was occuring in. I am trying to do this with one query instead of 12 as I would have to make 12 queries for every warehouse and that would be cumbersome. I now have one query for each warehouse with 12 fields named by the month. I would like this function to populate a textbox on a form when it loads using the month function to pick a query.field.

This is a rotating 8 months average of units sold and a whole bunch of calculations occur to the inventory fields using this field as part of the calcluation. As an example if the buy is reviewing inventory items during January the eight month average would be units sold May-Dec of last year, and of course for Febuary those field would advance one month Jun-Jan of this year etc.

Another issue is that there aren't sales for every item in the Inventory master file so some of those fields would remain 0, so the function would have to loop through the entire record.

First is this possible and second, if so how do I structure the statement

StrQuery = "What query.field"?

Function MonthlyAverage()
Dim strQuery As String
Select Case Month(Date)
Case 1
strQuery = "QueryToUseForJanuary"
Case 2
strQuery = "QueryToUseForFebruary"
End Select
Set rs = db.OpenRecordset(strQuery, , dbOpenDynamic)
End Function

 

[jal]

I now have one query for each warehouse with 12 fields named by the month.

Care to paste that one query? Because I am a little confused about your setup. Nor do I understand your function:

Function MonthlyAverage()
....
Set rs = db.OpenRecordset(strQuery, , dbOpenDynamic)
End Function

You end the function without assigning it a value. I would have expected something like:

Function MonthlyAverage() as Double
....
Set rs = db.OpenRecordset(strQuery, , dbOpenDynamic)
MonthlyAverage = rs("MonthlyAverage")
End Function