Group/Sort report using 2 date fields?

[Kimagine]

Hi all,

I am trying to produce a report that groups locations by open house dates. Where it's getting sticky is that some locations have a 1-day event (field OHDate1) but some have a 2-day event so there is a date in my OHDate2 field as well.

Let's say there are events on 9/13 only, 9/13 & 9/14, and 9/15 only. I need to accomplish this sorting/grouping:

9/13
(list centers having an event only on 9/13)
.
.
9/13 9/14
(list centers having an event on both 9/13 & 9/14_
.
.
9/15
(list centers having an event on 9/15)

I am currently grouping on OHDate1, then Location. The resulting report lumps the single-day locations in with the 2-day locations.

Any suggestions?
Thanks!
Kim

 

[dhookom]

This is the penalty for non-normalized tables. I don't understand how you combine dates for reporting. What would happen if you had some open houses singly on 9/14 or had three day events?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Kimagine]

For each location there can be 1 or 2 day open houses. I need to list each location that is having a single day, then separately list each location that is having a two-day, in date order. This report will go on to the advertising people - each location will have a flyer printed and mailed with their event date(s).

In my self-taught wisdom, I made two separate OH fields, perhaps that is incorrect. (I'm not quite understanding how I'm combining two dates either!)

Thanks,
Kim

 

[glalsop]

As a temporary solution, you could group on an expression similar to this:

=[OHDate1] & IIf(IsNull([OHDate2]),"",", ") & [OHDate2]

In the long term, you may want to consider re-designing your tables such that you have an 'EventID' that corresponds to a location and any number of dates, or a StartDate and EndDate as opposed to OHDate1 and OHDate2.

Good luck

-Gary

 

[Kimagine]

Figured it out!

In the grouping box, I grouped by OHDATE1 (grouping on Day) and then grouped by OHDATE2 (by day also) and then by LOCATION (each value), and gave each one a group header. I had to tidy the header titles up a bit, but it listed everything in the proper date order.

Thanks for your time, guys!
-Kim