I don't know if this possible, but would love some help on this.

I am building a Trend of data on a month by month basis for the year. I have built queries which gives me the data I want in the format I want, but I have a problem. I now want to use the same Trend for every year without having to change all my queries manually.

Here is a sample of one of the query elements:
Start: IIf([Jan_StartDate]<#01/01/2008#,#01/01/2008#,[Jan_StartDate])

I have a form in which I am capturing the Reporting Year in a Text Box and assigning it to a variable Reporting_Year. Is there any way that I can use the variable in the dates above?

Tks in advance for any suggestions or help

Ginny

Here is an article on how to pass variables between forms and queries using public variables and functions

David

Thanks David, that has given me a couple of ideas that I will try. I think I may be able to make a composite of the Day and Month and add it to the year variable to build the date at the start of each month. I will have to do the same for the end of the month also, and I think I may have a problem defining for the Leap Year!!

End: IIf([Jan_EndDate]>#31/01/2008#,#31/01/2008#,[Jan_EndDate])

Ginny

You can use the DateSerial function to create a date.

Dateserial syntax is DateSerial(Year,Month,Day) see Access help for more info.

If you input parameters with day = 0 then it will give you the last day of the previous month(takes care of 28th Feb,29th feb in leap years, and the 30 day months).

Thanks Rabbie, that's brilliant. Now all I have to do is implement it. I'm half way there, and this will put the finishing touch.

rgs
Ginny