date calculated field

[Xx_TownDawg_xX]

I totally agree. I posted up a fiscal calendar for your review. (and I don't ~know~ that's it's actually the first whatever after or before the whatever -- I just know that I need to tweak the code a little to calculate it correctly.)

FYI -- I originally started this project in Excel, because it was easier to setup a VLOOKUP than to figure out (for me) how to do it in Access... but once I got past the initial Excel worksheet for this and that, I realized that this was definitely an Access project and not an Excel project.

(Unfortunately my Access skills are pretty limited.)

 

[Preyzar]

I have a similar situation - our period ends do not coincide with the actual end of the month.

I've used a TimeDimension table that I've populated manually (copy/paste from Excel) with the date as the primary key. Each record describes which period/fiscal year and calendar year that date falls into.

Forex:

TransDate | Period | FiscalYear | CalYear

For another database where reports are generated by the week, I also included a field for "Week" and number them 1-52.

Writing period based queries is a snap with no complicated code required. The added data is a small sacrifice, lol.

It's obviously too late to be of help to you in this situation, but next time it may help.

 

[raskew]

It's doable and I'll work on it later today.

The question is -- what's the point? Can someone help me out here?

I'd love to hear the explanation of how this is a good route to take.

Bob

 

[Xx_TownDawg_xX]

... have a similar situation - our period ends do not coincide with ...

It's definitely not too late. I'm really struggling with every aspect of Access 2007. Back in the day, I used to be halfway good at it, but I sure can't say that now.

Preyzar, if you'd upload an example or better explanation, be glad to study it.

Raskew, I totally understand your frustration, and appreciate your help. Your original coding was WONDERFUL, and would have worked flawlessly if ... but hey, I've stopped trying to make sense of some things, you know?..

 

[gemma-the-husky]

ive not read all this, but format command will change a date into a weekno

format(mydate,"ww")

there are certain switches to determine when week1 starts.

can you use this to determine your calendar

eg
month1 = weeks 1-4
month2 = weeks 5-8 etc

 

[raskew]

TownDawg

Comparing your PDF against "The START DATE for each fiscal month is the the first MONDAY before the last Friday in the previous calendar..."

It is not consistent for Nov 07, Feb May Aug 08, and Jan May Oct 09. We need to know the logic before coming up with an accurate formula.

Bob

 

[Xx_TownDawg_xX]

raskew -- "The START DATE for each fiscal month is the the first.." -- the words were mine. I have no idea how the months were created. When I first started, I was using Excel for the project, and my original plan was using a VLOOKUP function.

I'm still using that table, that has every START DATE and STOP DATE in it (same as the PDF file) -- if it's easier to do something equivalent to a VLOOKUP -- I'm ok with that also.

 

[raskew]

TownDawg -

I've played with this for a day or two or three and have yet to come up with a repeatable formula that'll produce all of those dates.

You really need to get those folks to explain their logic. My guess is that they're just winging it, and don't have a real plan but instead are manually plugging-in whatever feels good at the moment, which is going to leave us without a plan. There doesn't appear to be any consistent logic behind it. You can go with your PDF data, but the question becomes: What happens when you run out of dates? I'd love for you to prove me wrong!

Take a look at the DLookup function. Not my area of expertise but think this is close to the Excel VLookup. (Somebody please jump in here!)

If you have more info, please post back.

Bob

 

[gemma-the-husky]

i would have a table of month ends


periodend_date
25 jan 08
26 feb 08

etc

then given a date - testdate

usedate = nz(dmin("periodend_date","datestables","periodend_date >= " & testdate),0)

[you might need # characters round testdate in the dmin above]

this dmin sets usedate to be the lowest period end date occurring on or after the testdate

---------
and given a period end of jan-25-09, you can just format it mmm-yy to get jan-09

--------
this is fine as long as the period end does not run into the following month.
if it does you need to add another column to the table to show the fiscal_month, and do a dlookup based on the usedate just retrieved, to read the fiscal date.

--------
as an alternative i noted before that you can change a date into a weekno

weekno = format(testdate,"ww") - returning a number in the range 1 to 53
this is more efficient than the dlookups, and you might be able to use this to determine the fiscal period instead

 

[Xx_TownDawg_xX]

Good morning raskew and gemma-the-husky.

I was thinking the following sort of pseudo-code.

Given “testdate”, determine year(testdate), and lookup the start of that fiscal year in a tbl_lookup. The one constant we do have is 7 days to a week. Determine how many “weeks” have elapsed from the “start of that fiscal year”. Recognizing that the other constant is a 4-4-5, 4-4-5, 4-4-5, 4-4-5 calendar, it should be fairly easy to determine what “fiscal month” applies to the number of “weeks” that have elapsed from the “start of that fiscal year”.

Would something like that work, if I knew how to code that? I would not mind having to maintain/update the code occasionally, I just need something to work for the archived 2008 date I've already entered, and the 2009 data that ~will~ be entered. In 2010, I can review the code again.

 

[raskew]

Gemma, TownDawg -

Attached is a zipped file (A2003) with Table1 being an exact copy of the PDF.

Could you provide code to demonstrate your last post.


Bob

 

[Xx_TownDawg_xX]

usedate = nz(dmin("periodend_date","datestables","periodend_ date >= " & testdate),0)

usedate: Nz(DMin("[Filter_qFiscal]![tbl_stop]","[Filter_qFiscal]","[Filter_qFiscal]![tbl_stop] > = " & [Audit_Date]),0)

 

[Xx_TownDawg_xX]

hMmmm.. it's not going to be THAT easy. I have a problem with how i have linked my tables/queries together. Unfortunately I don't understand it enough to explain it.

 

[raskew]

Hi -

A little info, based on your PDF (see Post #31 to download table)

The FY runs Oct to Oct.
FY-2008: 10/29/07 - 10/26/08
FY-2009: 10/27/08 - 10/25/09
FY-2008: 10/26/09 - ?

The FY-Month starts on a Monday and ends on a Sunday, except for Nov-09 which both starts and ends on a Monday.

The StartDay is anywhere from 2 to 9 days from the end of the Start Month.
The StopDay is anywhere from 3 to 10 days from the end of the End Month.

There is no consistency (that I could detect) in how Start and Stop days are calculated.

I couldn't get the dMin solution to work. As an alternative try this query, modifying table/field names as necessary. When prompted, enter a date in mm/dd/yy format.

PARAMETERS [Enter TestDate] DateTime;
SELECT
    Table1.tbl_Month
  , Table1.tbl_Start
  , Table1.tbl_Stop
FROM
   Table1
WHERE
   (((Table1.tbl_Start)<=[Enter TestDate]) 
AND
   ((Table1.tbl_Stop)>=[Enter TestDate]));

Bob

 

[Xx_TownDawg_xX]

The FY-Month starts on a Monday and ends on a Sunday, except for Nov-09 which both starts and ends on a Monday.

That was my fault. I guessed at Oct-09, and forgot to remove it from the PDF.

 

[Xx_TownDawg_xX]

Raskew -- I'm not completely sure what all to change/modify.

PARAMETERS [Enter TestDate] DateTime;
SELECT
Filter.qFiscal.tbl_code
, Filter.qFiscal.tbl_start
, Filter.qFiscal.tbl_stop
FROM
Filter.qFiscal
WHERE
(((Filter.qFiscal.tbl_start)<=[Enter TestDate])
AND
((Filter.qFiscal.tbl_stop)>=[Enter TestDate

 

[raskew]

Try:

PARAMETERS [Enter TestDate] DateTime;
SELECT
    qFiscal.tbl_Code
  , qFiscal.tbl_Start
  , qFiscal.tbl_Stop
FROM
   qFiscal
WHERE
   (((qFiscal.tbl_Start)<=[Enter TestDate]) 
AND
   ((qFiscal.tbl_Stop)>=[Enter TestDate]));

Bob

 

[raskew]

I don't understand the Filter.qFiscal business.

If the above doesn't work for you, please describe the Filter.qFiscal and post a copy of your query SQL.

Bob

 

[Xx_TownDawg_xX]

Filter is the table name. qFiscal is the field name.

 

[raskew]

Filter is the table name. qFiscal is the field name

Then what are tbl_code, tbl_start and tbl_stop??

Suggest you download the table I provided and apply the query to it.

Bob