PLEASE HELP: Using Months as a Record Source (1 Viewer)

[esipp]

I am trying to force the Detail section of my report to repeat for each month between two dates.

I have tried doing a VB counter to repeat the detail in On_Format event, changing the dates using Unbound fields based on the counter. However, the On_Format event only occurs once and bound data in a sub-report does not update.

IS THERE A WAY to use a calendar as a record source? For example, an automatic record (refreshed, new Detail) for each month between StartDate and EndDate. That way, I can repeat and all data will tie.

Right now, the only way I can get my report to output correctly is to have a table with as many dummy records as months I want to show up on my report. This is so cumbersome.

Example of a Table:

StartDate EndDate ChargeType ChargeAmount
1/1/04 4/30/04 Gas $100
1/1/04 3/31/04 Insurance $50
4/1/04 4/30/04 Insurance $75

Desired Output (notice no month of February)

January
Total Charges 150

February
Total Charges 150

March
Total Charges 150

April
Total Charges 175

 

[JimH]

This may not be what your looking for, but your table design is probably not exactly wriiten the best way possible. You might consider

ExpenseDate Item Purchased Cost

Then run your output grouping on all items purchased in any given month.

HTH
Jim

 

[esipp]

I see what you are saying, and I would normally do that for single, random charges. What I'm dealing with is rent charges. So, for example, an office space rents for 5 years and has one step up in rent in the middle of year 2. I need to prepare monthly payment coupons IN ADVANCE, but I only have start and end dates to work with.

Though I need a coupon for January, February, March, etc...
I don't want a charge record for each month. I deal with hundreds of tenants over long time frames, so having individual month charge records is unacceptably inefficient.

 

[The_Doc_Man]

Access doesn't work with implied things, only explicit things. The way to do this is to build a table of months and years (or maybe just a table of the first of each month and year as a formal date). Then write update queries based on this calendar table as a recordsource for the stuff to be written or reported or whatever.

The PROBLEM you encountered is that nowhere in Windows or Access does an actual calendar exist. Days are just numbers. No table bothers to hold these numbers unless you tell Access to do so (by building the table I just described to you.) When you want a date-formatted thing, a system subroutine looks at the day number and computes the calendar equivalent. When you schedule something (say, using Task Scheduler), all you do is store a rule. Every so often, Task Scheduler wakes up, computes the day number (and perhaps fraction of a day), then compares that time to the elements in its work list. If the current conditions match an element of the work list, Windows does it. But even the Task Scheduler doesn't really keep a calendar with a check-mark on. Ditto, the calender in Outlook. It is dynamically formatted, not statically stored.

Since Access uses a "sparse" algorithm for table storage, it doesn't CARE that something doesn't exist - but you can't link to what doesn't exist, now, can you? And that is why you have the problem. You must BUILD the table in order to use it constructively.

Do you know what a calendar control REALLY does? It just mucks about those special "day numbers" and shows you the grouped, formatted array of days. And that's what gets stored in a date field - that "day number" - which is just a linear number of days since a reference date.