Report By fiscal dates 1

[andrewhh82]

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.

 

[Remou]

Hi
Have you seen:
Online Resources & Help for Date/Time Functions in Access
faq705-3277
Particularly
Get the Fiscal Year or Month of a Particular Date in Access 2000

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

From that FAQ.

 

[andrewhh82]

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.

 

[Remou]

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.

 

[sdk]

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

 

[andrewhh82]

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.