Date format in query that pulls information from a report 1

[storl2]

Ok, I'm not sure if this can be done, but here is what I am trying to do.

I have a report of classes taken that is currently grouped by actual year, but I need it to be grouped by fiscal year. I am trying to create a query that looks at the group header of year when determining the results. In the criteria of the date field of the query, I am trying to do something like:

between #2/1/Reports![Report]![Year]# and #1/31/Reports![Report]![Year +1]#

So like if I have the year 2003 as the group header, the query will pull classes between 2/1/2003 and 1/31/2004 as the results for that particular group, and then go on to the next year.

The above syntax doesn't work, so does anyone have any idea how I could word this to make it work?

 

[THWatson]

storl2
Given that the report is based on values from the query, I'm not sure you can read the Group Header from the Report back into your query.

However, you could set up your fiscal year in the query.

Have a column in your query that has a structure something such as the following...
Year([ClassDate])-IIf([ClassDate]<DateSerial(Year([ClassDate]),2,1),1,0)+1

That will separate the class years into the proper years, based on February 1 through January 31. If that doesn't do it exactly as you want, fix the values in the DateSerial function so they are correct.

Then put in Criteria to select the year you want to see in the report. You can enter the desired criteria through a parameter prompt in your query [Enter Year to check] or, better still, set up a form in which you enter the criteria in a text box, and put Forms!FormName!TextBox as the column criteria in your query.

Tom

 

[storl2]

Tom,

That was perfect. I actually dropped the "+1" off of the end and changed a couple of other things to give me the correct fiscal year. FY 2003 for us would be 2-1-2003 through 1-31-2004. Would you mind explaining the syntax of that? I have another report where I need to do something similar and would love to know how that works. I checked the help files, but they were not very helpful.

I thought you would like to know that I actually didn't even have to refer to the year as a criteria for the column, it took care of all that in the report when it did the grouping.

 

[THWatson]

storl2
Glad it worked for you...with the tweaking you did. What I gave you was, more or less, a copy of a query expression I had that makes use of the fiscal year idea.

For an explanation, here's a link to Article 210249 in the Microsoft Knowledge Base...

http://support.microsoft.com/default.aspx?kbid=210249

Essentially, DateSerial uses the Year, Month, Day approach. So DateSerial(Year(Date()), 7, 1) would give you July 7, 2004. But then there are additional expressions that indicate the first day of the month, or the last day of the month, etc.

Also, in faq705-3277 on this site, there are a bunch of links to other sites where you can find useful Date information.

If you need something more, post back.

Tom