Creating a report with a start date and an end date

[slacy]

I have a data base where we keep track of radio ads that we run. Say the radio spot runs starting Jan. 1 and ending on March 31. I want the report to list January and the dates of the radio spot and then say it runs thru March. I want to have a heading that lists Feb and March. I know how to get the month for the start date but in this case the end date is important to us to.

Thanks

[Rabbit]

I don't understand your question. If you are trying to get the end month, and you know how to get the start month, all you have to do is copy what you did to get the start month and replace the start date field with the end date field.

[slacy]

Okay - If I want a schedule pulled from this....we have a radio ad that runs from Jan. 1 thru Mar. 31. I want a report that shows my ads for Jan., Feb. and Mar. Currently I run the report by the start date, but I want the months in between too. Does this make sense?

[Rabbit]

I think you're going to have to be more specific. All I can say at this point is to run the report by start date and end date. To truly understand what you are asking for, you need to show us the SQL you're using, some sample data, sample results from that sample data, and table/field definitions.

[GKJR]

Do you have a table that keeps track of the ads that have run? If you have a list of ads with their corresponding dates, you can just group your records by date on your report and select "by month" in the grouping options. Use the id field for the specific ad in question to filter the records in your query.

[slacy]

Do you have a table that keeps track of the ads that have run? If you have a list of ads with their corresponding dates, you can just group your records by date on your report and select "by month" in the grouping options. Use the id field for the specific ad in question to filter the records in your query.

Yes, I do have a table, but when they put in the start date it might be 1/1/14 and the end date might be 3/31/14. So this billboard ad runs continuously until 3/31. When I created the schedule I had it pull by the month so they knew what was running at a glance and if we had any months open - so the schedule misses Feb. & Mar. unless they enter it with just one month at a time, which is time consuming for them. Thanks for your help!

[GKJR]

So when they create a record they are only choosing a month instead of an actual date? That doesn't seem to be the best way to do it. With a date picker box it should be easy enough to choose an actual date, such as the last day of the month. You could display any date by the month it is in with formatting or using the date functions in VBA.

Do you have two different tables storing this data? To me it sounds like start date and end date should be stored in the table for "Ads", and you should have another table for "Ad Occurrences" or whatever you want to call it. This table would reference the AdID in the Ads table.

If you base your report on the table for Ad Occurrences (your query should still have the "Ads" table in it - join by showing all records in Ad Occurrences and filter by Ad Name in the Ad table), then you could create a group for the "Ad Date" and choose the option for grouping by month. The header in this section would then show the month, and the detail section could show the actual date and any other fields you want to include. You can put a text box control for the date in both the header and detail sections.

[NeoPa]

Are you looking for date range overlaps? Where you have a range you're looking for and records which themselves reflect a range?

Check out Time Interval Overlapping (MS Access 2003).