Way to display records in bi-weekly groupings

[PeakH]

I have a bunch of different reports, most of which open from a form using combo boxes to select the month, the year, and then which report to open.
I have just been requested to make a bi-weekly sales report and am trying to figure out how to use the same simple combo box idea to select the bi-weekly date range. I realize that if I just use the date range, it will not be the same dates from year to year, which could require a too long list that just continues from one year to the next. If I use week numbers, only the odd numbers would be displayed, since week 1 and 2 are together, 3 and 4 are together and so on. Plus it's hard to remember what week number we're on or what week number corresponds with the date that you might be trying to print a report for, especially if you're looking to print old reports.

I'm just looking to brainstorm some user friendly/intuitive ways to make bi-weekly date range selection for reporting. Any/all ideas are appreciated!

Thank you in advance.

[pbaldy]

It's been a while, but I recall creating a table with StartDate and EndDate, and populated it with a few years worth of data. It's not difficult to code a function to quickly populate that table. Then in a query you can join to that table to find the period for any date (using a non-equi join).

[PeakH]

Yeah, I did create a table like that before for pay periods. My worry with this one is that the list will end up being too long to choose from, but what I will try is ordering them to that dates in the current year are listed first and see how that goes.

I created a form with an update button, that populated the fields:

Me.StartDate.Value = (([BiWeekID] - 1) * 7) + #1/1/2017#
Me.EndDate.Value = ((([BiWeekID] - 1) * 7) + 13) + #1/1/2017#

This gave me two week intervals, but then I had to create a delete query that deleted all the records with an even numbered auto ID.

[ArviLaanemets]

Create a table (or query) of dates, started at some certain weekday (p.e. Monday, or Sunday when you prefer so) and populated at 14-days interval. Then use this as source of your combo. The end date you can calculate as start date + 14, and the period is calculated as >= StartDate and < (StartDate+14)