Checking Date Ranges in a Report Query

[rjgriffin46]

Our Kids Feeding database tracks a process where a week's worth of food is delivered to participating sites the week, generally Friday, before the meals are served. It also has records that indicate when the entire program or individual sites will not be in service and thus should not receive food.

Key tables are:

1. Sites
   The site name and address for delivery.
2. Site Changes
   Indicates the number of meals the site requires.
3. Menus and Items
   A list of the items for each menu.
4. Menu Groups
   Indicates what day of the week each menu is served.
5. Non Service Days
   The from, and to dates (if any) and site that will not be serving or and indicator that there will be no meals delivered program wide.
6. Delivery and Serving
   Indicates the serving start date (usually a Monday) and the number of serving days (usually 5).

Using the above I can generate an Items Site Serving query that has a record for each site, for each serving day (Monday through Friday) and for each item on the menu. This is part of the record source for an Invoice and Delivery Receipt report (attached) that prints a page for each site. This report is used by our kitchen staff to prepare and allocate the necessary menu items and as a receipt to be signed by each site upon delivery.

What I am unsure of is how to check if there are program wide or if any of the sites have a non service record (or records) within the serving period. There could be multiple records for both program wide and an individual site and any of these records could be for a single date or a date range. The date range could be wholly or partially within the serving period. The objective is to omit the menu items for any site with a non service record for dates that match a serving date. If there is a program wide non service record with a date(s) within the serving period then the items are omitted for all sites for that date.



Here's an example:

Site A has a non service date of Monday 10/8/18. For the serving period of 10/8 to 10/12/18, Site A would show nothing for Monday and the menus and items for Tuesday through Friday. On the attached sample report you will notice several of the pages start with Tuesday because they have non service records for Monday.

My thinking is to generate a record from the Non Service Days table for each date of non service that has the Site or the program wide indicator and the non service date. So if the non service record had a start date of 10/8/18 and an end date of 10/10/18 there would be 3 records generated. I could then join or lookup to this table from the Items Site Serving query and omit any items for any days that match date and site.

I'm not sure how to generate multiple records from a single record nor if it is the best approach. So any ideas and thoughts on the approach would be appreciated as always.

Hot Sites for 10-8-18 Excluding Not in Service.zipHot Sites for 10-8-18 Excluding Not in Service.zip

[Peter M]

This has been outstanding for a month so might not be necessary, but have you tried a query with an "IIF" (Instant-IF) condition where If the Date is a non-service date, then Menu = "" otherwise, use the regular menu field.

[rjgriffin46]

Peter:

I managed to come up with a solution to this problem. Thanks for your interest