Setting the Year Variable

[Ginny2222]

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

 

[DCrake]

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

David

 

[Ginny2222]

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

 

[Rabbie]

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).

 

[Ginny2222]

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