fiscal months

R

rascal

I have created a database that requires other people to fill out forms. Part
of the form requires the date, day and fiscal period to be entered. I have
inserted a calendar control and the date and day automatically populate those
fields but I am having problems firguring out how to get the fiscal period to
automatically populate. I am new to databases and have been able to figure
out most of the things I needed to do, but this one. Since a lot of the data
is tracked by fiscal period and I find that people sometimes forget to enter
the period it would be easier to have it entered automatically. Our fiscal
periods run like this for 2006:
period 1 is from 1/1/06 to 1/28/06
period 2 is from 1/29/06 to 2/28/06
period 3 is from 2/29/06 to 4/1/06 etc.
Any suggestions would be most welcome.
 
J

John Vinson

I have created a database that requires other people to fill out forms. Part
of the form requires the date, day and fiscal period to be entered. I have
inserted a calendar control and the date and day automatically populate those
fields but I am having problems firguring out how to get the fiscal period to
automatically populate. I am new to databases and have been able to figure
out most of the things I needed to do, but this one. Since a lot of the data
is tracked by fiscal period and I find that people sometimes forget to enter
the period it would be easier to have it entered automatically. Our fiscal
periods run like this for 2006:
period 1 is from 1/1/06 to 1/28/06
period 2 is from 1/29/06 to 2/28/06
period 3 is from 2/29/06 to 4/1/06 etc.
Any suggestions would be most welcome.
Click to expand...

I'd suggest having a FiscalMonth table with three fields - DateStart,
DateEnd, and PeriodName. Join this to your Query with a "non equi
join" - start by joining the date field to DateStart and then edit the
SQL code from

ON yourtable.Datefield = FiscalMonth.DateStart

to

ON yourtable.Datefield >= FiscalMonth.DateStart AND
yourtable.Datefield <=FiscalMonth.DateEnd

Note that if period 3 starts on 2/29/06 you're using a very odd
calendar! <g>

John W. Vinson[MVP]
 
R

rascal

Thanks I'll give that a try and see if it works. Sorry about the mix up in
dates there, I've been working on this for a while and was very tired when I
posted the my problem.
 
J

John Vinson

Thanks I'll give that a try and see if it works. Sorry about the mix up in
dates there, I've been working on this for a while and was very tired when I
posted the my problem.
Click to expand...

That's what it looked like... and why I put the <g> (grin). Good luck,
post back if you have problems with it - posting the SQL of your query
if you do.


John W. Vinson[MVP]
 
R

rascal

Sorry to bother you again, but what is a "non equi join" when I join them
together and go into the query it says "inner join".
Thanks for your help
 
K

KARL DEWEY

In looking at your example all of the periods are 28 days long. If this is
true then it can be calculated like --
Int((DateDiff("d",("1/1/" & DatePart("yyyy",[YOURDATE])),[YOURDATE])+1)/28)+1
 
R

rascal

Thanks for your help John, I took a break from it for the rest of the day and
when I went back to it this morning it all seemed to come together and
finally it works.
Thanks again :)
 
Top