Custom function with "automatic" variables

[sehmke]

Hello Forum,

I have a table which stores sales results per month. There is a field YEAR and and a FIELD month.

But sometimes I want a query to give me the results per QUARTER.

To define the quarters, I am able to do this with a Totals Query, by using the following formula:

Quarter: [Year] & " Q" & DatePart("q";DateSerial([Year];[Month];1);1;0)

Since there is no real date field, I can't use a Crosstab query.

My question is simple:

How can I avoid having to type out the above formula in a query? It is rather complex as you can see. Is there a way to store this in a Module or a Function?

I am aware of the "Build Custom Function" procedures, but I reckon it would still need variables. E.g. Quarter([Year], [Month]). This is already simpler than above, but can it be done without variables? Can a custom function "assume" where it has to find the data to come up with the result?

I like to refer to the function NOW(), which has no variables and gives the current date. It would be nice if a function QUARTER() could do the same but then as explained above...

Good luck.
Stefan

 

[Bat17]

NOW() returns todays date/time stamp.
what value would you expect QUARTER() to return if you don't pass variables into it? It could be made to return the current Quarter but it looks as if you want the quarter that you data is in, and for that you will have to pass it some data.

Peter