Reporting Dates different for each month.

[kjpreston]

I have been asked to set up a database that will allow me to pull a report of all reports that are due for the next month. Unfortunately the due day is not always the same. for example I have several reports that are due the 5th business day of the month and at least one report that is due on the 5th calendar day of the month. So far i have set up a table with all the reports and thier corresponding time frames (i.e. monthly 5th business day, monthly 5th calendar day) (monthly and number of days are in separate fields). I also have a table with the months, the number of business day and the number of calendar days in each month and a table with a list of all pertainent holidays. I am now, however, stuck. I can't seem to figure out a way to get the information I need on the fly as opposed to making an entry for each accurence of a report. Any ideas?

 

[MichaelRed]

See the EVAL function. You can create the 'formulas" for the dates as a field in the table and use Eval to generate the actual dates.

You can also build a table far into the future (5 to 10 years?) and write normal / standard functions to be used in conjunction with queries to update the individual date fields for the reports. There are discussions of the 'table of dates' in htese (Tek-Tips) fora, suggesting the basic information normally included and their uses.

Individual functions for the date calculations tou mention are relatively trivial, and could easily be implemented to be used to up date the table. I find that holidays are particularly changeable, and would usually place them in a seperate table.



MichaelRed

 

[kjpreston]

Thanks for the information. I have never used the EVAL function before, but I will give it a shot. Thanks again Michael

 

[lameid]

When I have done similar things albeit less complex, I just stored paramaters for the dateadd function or other rules and used appropriate cross joins and IIF's to get at a universal set of rules. That's just an alternative to Eval. Depending on your situation and permutations it may be better or worse.

 

[MichaelRed]

While I agree that the peocesses can be accomplished per the suggestion - and in numerious variations, I suggest the use of EVAL, as it embedds the functions / algorythims directly into and as part of the datase.



MichaelRed