Group records by month

[jabarlee]

Hello everyone



I have a simple table:

Event_ID	Event	Start_Date	End_Date
1	event1	22/02/2017	08/03/2017
2	event2	03/08/2017	24/12/2017
3	event3	05/03/2017	15/10/2017
4	event4	08/04/2017	17/07/2017
5	event5	20/07/2017	05/09/2017

I want to show in a form the events that are ongoing during each month up to the present date, starting from the oldest Start_Date. For example:

February 2017
event1

March 2017
event1
event3

April 2017
event3
event4

For each month the records that will appear are those with a Start_Date < "month in question" < End_Date

Any ideas ?

[June7]

Calculate field(s) that extract year and month number. Calculate a field that extracts the month name and year and use that field for display.

Options:

Yr: Year(Start_Date)

Mo: Month(Start_Date)

YrMo: Format(Start_Date, "YYMM")

MoYr: Format(Start Date, "MMMM YYYY")


You can sort records on form with these values but the grouping you show is best done in a report using its Sorting & Grouping design features.


You are showing international date structure. Might have some issues. Review: http://allenbrowne.com/ser-36.html

[jabarlee]

Thank you for your answer.
Actually, I think that I will manage to handle the formatting of the dates. And the issues you're referring to, I encountered them already some days ago, I spent half a day trying to figure them out.
Me real problem is the grouping part, in a form. I know that a report would be much easier, but it doesn't suit me needs in this case

[June7]

Grouping doesn't work on a form. You can sort by year, month, event but won't have the 'grouped' appearance.

A grouped report can be displayed on a form, just can't do edits in it.

[jabarlee]

Thanx again.
I followed your advice and formatted the dates so I can compare them by month/year.
I used this:

Format([Med_StartDate], 'yyyymm')
Format([Med_EndDate], 'yyyymm')
curmonth = Format([Med_CurDate], 'yyyymm')

So I can compare Med_CurDate with Med_StartDate and Med_EndDate
I also realized what you wrote, that I cannot do the grouping in a form, so I created a subreport and put it in my form. I will find another way to get the functionality I was looking for.

My -new- problem is with my query's criteria. When I use the following WHERE clause, I get a 'type mismatch error', that I found out it has to do with the NULL fields:

"WHERE (Pt_ID = " & Me.Pt_ID & ") AND (Format([Med_StartDate], 'yyyymm') <= " & curmonth & ") AND (Med_EndDate IS NOT NULL) AND (Format([Med_EndDate], 'yyyymm') >= " & curmonth & ") ;"

Even if I try to exclude the rows where the [Med_EndDate] field is null, the (Format([Med_EndDate], 'yyyymm') >= " & curmonth & " still produces the 'type mismatch' error. The error does not occur when I have no Nulls in the table.

What would be the workaround to this ? Shouldn't the

AND (Med_EndDate IS NOT NULL)

get rid of the nulls?

[June7]

I tested and do not get error. Cannot replicate issue.

Format() returns an empty string if the field is Null or empty string.

[jabarlee]

Well, I think I found out where te problem was:

I have declared "curmonth" variable as a number, and only very late did I realize that Format() returns a string. So, I cannot really compare them, BUT Access did not complain about it when I had only one criterion. When I enter the second HWERE clause, it did complain. That threw me off and I did not see the real problem, making me think that it was the null values