Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report By fiscal dates 1

Status
Not open for further replies.
Oct 13, 2005
17
US
I am building a Access 2000 database that tracks classes attended by people in my group. i have a table for attendance with the date they attended as field (mm/dd/yyyy). I need to print reports by Fiscal Month (ie. Fiscal Month 9 includes 10/1/05 - 10/31/05) My database currently is simple with 3 tables Employees, Events, and Attendance. I thought of making some type of table with Fiscal month and year but doesn't seem to work out. I don't want to enter the fiscal month and year for every attendance record when i have actual date attended. Any help would be great, i am a novice.
 
I'm not sure if that helps. Maybe i'm not using it right, but it seems like a way to just add the fields fiscalMonth and FiscalYear to a table. Maybe i'm just not imlementing right. I basically won't to click a report that shows the employees that attended classes in this or any fiscal month or year.
 
It seems to me to be about queries. How about this, which can be pasted into the SQL view of a query:
[tt]SELECT Format(DateAdd("m",-1,[Attendances]),"m yyyy") AS FiscalMonthYear
FROM tblAttendances;[/tt]
Where Attendances is the name of the date field and tblAttendances is the name of the table.

The above idea is based on your statement that "Fiscal Month 9 includes 10/1/05 - 10/31/05", that is, one month less than the date month.
 
Hi

The links by Remou should guide you, and you should not have to map dates to a table to get the results you need. Mapping may be easier now, but getting this right makes life easier in the lng run.

The MS link should help. The first guide is using inbuilt fucntions in a query to get what you want; the second guide shows how to build a simple function. From your description, use the first part:

Cut&Paste:
FYear: Year([FieldName])-IIf([FieldName]< _
DateSerial(Year([FieldName]),9,15),1,0)

into a field in the QBE, and cut & paste:
FMonth: (Month([FieldName])+IIf(Day([FieldName])<15,9,10)-1) Mod 12+1

into another field. Substitute FieldName for the name of your field in both functions.

The FiscalYear here (Oz) is Jul->Jun; looking at these (and your prob) the fiscal year may be somewhat different (Sept 15?), so I won't interpret exactly. Also you should be ware that this is precise (first part of moth vs second half of month yeilds different results) - if this is an issue we can create a specific function for you.

Cheers

Steve


 
Got it. I used the expressions in the query builder. Had to slightly alter it. used the following incase anyone else needs this. this is for a fiscal year starting on Feb 1.

FYear: Year([fldDate])-IIf([fldDate]<DateSerial(Year([fldDate]),2,1),1,0)

FMonth: Month([fldDate])-IIf([fldDate]<DateSerial(Year([fldDate]),2,1),-11,1)

Now i just run a report from that query. Thanks for everyone who helped.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top