VBA in query

[Cpac]

Hello,
I am trying to build a query which calls a vba function to define
output fields. My table has columns for each month - i.e. field name
M1, M2, M3 -> M12


I would like to pass a function variable based on the current month -
i.e. 7 = July

Once the function receives the month number, it determines how to
structure the query. I was planning to use a case statement with the
following logic:

Case = 7
place code to add M1+M2+M3+M4+M5+M6+M7

Can someone please let me know how to build the function and call it
from a query.

Thanks

 

[Graham R Seach]

You can call any public function from a query, however, you won't be able to
have it modify the query that called it. The best you're going to be able to
do is use code to build a new query at runtime - then run *that* query.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Cpac]

Hello Graham,
I know how to build a query in code but not sure how to base a report
on a query designed within a module. Could you please provide a simple
example? Thanks for your help!

 

[Graham R Seach]

I'd suggest creating a dummy query whose SQL property you can change as
necessary. Since you already know how to build an SQL statement, all you
need to know is how to modify an existing stored query on which to base your
report.

CurrentDb.QueryDefs("qryMyReportQuery").SQL = "SELECT * FROM MyTable"

Then just call the report, using the utility query as its source:
DoCmd.OpenReport "rptMyReport", acViewNormal, "ryMyReportQuery"

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia