Multiple Numeric Values passed to Function as Query Criteria

[JessicaGomo]

I am running monthly reports, that also include year to date metrics. The numeric month and number of months to include for year to date, is all setup through vba code and a user form. When I pass the individual numeric month (ex. 1) in the function, the query works as expected. When I try to pass multiple months (ex. 1,2,3) I get blank results. If I manually remove the function as the "criteria" and put "In(1,2,3)" as the criteria, it works fine. I am trying to setup automation for this report so would like to see if I can find a way to pass multiple values in the function. I have tried both numeric and string data types and still with no luck. I appreciate any suggestions!

 

[plog]

Should this query always produce year to date results? If so, to truly make it efficient you wouldn't pass data to it at all to do this--you would have the query itself determine what data to show based on today's date.

Something like

WHERE Year([YourDateFieldHere])=Year(Date()) AND Month([YourDateFieldHere])<=Month(Date())

 

[JessicaGomo]

Thanks for the quick response! It produces monthly results and year to date results.

 

[plog]

If its a report based on that query, you can use the DoCmd.OpenReport method to open the report and apply criteria when you do so. That way you could open the report Year to date as well as for monthly. No need to touch the query.

 

[Simon_MT]

If you are trying to do quarterly or half and yearly reporting you could create a table to with YearNo and MonthNo and extract which period or alternatively have a StartMonth and EndMonth.

Simon

 

[jdraw]

For clarity, you might show us the vba code for the function.. That would keep the guessing to a minimum.

If you are doing year to date reports, that usually means "from the start of the year". So, if your function deals with month 5 for example, year to date would include months 1,2,3,4 and 5.
Now you haven't mentioned calendar year or fiscal year, but that is a separate issue.

 

[arnelgp]

show us the function.