annual report problem

[Seasider]

I have spent countless hours trying to figure out a way to create a query that will enable me to print an annual report but have not been able to figure out how to set up the criteria.

I have a form on which users can enter data about projects. I want to produce an annual report to show what projects are still active throughout the fiscal year. The start of the fiscal year is April 1 and the end is March 31. This report, of course, will be run each year.

The client wanted a date field to enter when the project was completed; it is called DOHCompleted.

I seem to be totally confused about how to include all the variable criteria needed. For example,

- projects completed prior to the beginning of the current fiscal year should not be included

- projects still active at the end of the fiscal year should be included, including ones that were active in past years that are still active (meaning DOHCompleted is empty)

- projects with a DOHCompleted date that is during the current fiscal year need to be included

Also need to take into consideration that the report will probably not be run on exactly the end of the fiscal year.

I will also need to produce quarterly reports during the fiscal year. Haven't even begun to figure that one.

Can anyone help with this one. Hopefully I have provided enough information and not confused the question too much.

Seasider

 

[namliam]

(dohCompleted >= #01-APR-2002#
AND dohCompleted <=#31-mar-2003#)
OR (dohCompleted is null
AND dohStarted <= #31-mar-2003#)
The first part selecting projects ending in the fiscal year, the second part selecting projects that start in or before the fiscal year but not yet have ended...

Hope that helps

Also i bet searching for fiscal year on the forum will get you going on other things regarding quarters and stuff. I know its out there.

Regards

The Mailman

 

[Seasider]

fiscal year

Thanks Mailman for the response. Your proposed solution would not work for me because the year completed will change each year. I need it to calculate the something like Now()-1 (meaning the previous year).

I will look for threads with fiscal year.

Appreciate your help.

Seasider

 

[Seasider]

annual report problem update

I am still trying to sort this problem. This is the part of my query criteria that is not working.

PastYr: IIf(DatePart("yyyy",[dohcompleted]=Now()-1),"y","n")

The above calculated field is meant to filter dohcompleted for any record that has a year that is one year prior to the current year. For example, the current year is 2003 so I am trying to find the year 2002 in dohcompleted.

Problem is that the results are including records that have year in dohcompleted that is 2003.

Does anyone know what I am doing wrong or a suggestion on another way to filter in the query? I have tried everything I can think of.

Thanks,
Seasider

 

[Seasider]

I have finally sorted out this one myself. Had to use nested queries with multiple calculated expressions for current, past years and so on.

Appreciate any help I get from this forum.

Seasider