Date Range Picker

[Steven19]

I want to understand how to create this common date picker.

Normally I see this in Combo boxes in many databases.

2020/2021
2021/2022
2022/2023


and so on.

These dates are generally 1st April to 31st March

1. How would I construct this?
2. How could I have it auto create a new range ie, when its near to 2023/2024 year range?
3. Could I use this to filter queries from a user defined form?

Regards

[ranman256]

In a form put 2 text boxes: txtStartDate, txtEndDate
you can set default dates for them.

the query would use:
select *from table where [datefld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEmdDate

you can add a combo to set the date span by picking a year.

[Welshgasman]

If I had to do exactly what you are asking, I'd probably do it this way
Create a table that holds start and end date per range
Create the visible combo that shows Year(StartDate) & "/" Year(EndDate) and the start and end dates as extra columns.
Then when you select an entry, you use those other columns as criteria.

This also allows you to specify other dates, not always 1st April and 31st March.

If that was always the case, then you could just get by with a year column, and create the other values on the fly.?, but I think that would be risky.

[CJ_London]

you can create a range in a query to use as your recordsource. Assuming your range is always a year

1. create a table, populate it with 1 to 99 in a field called num. This will give you 100 periods. Use more or less numbers as required
2. in a query use this something like this syntax

SELECT 2000+[num] & "/" & 2000+[num]+1 AS Period, DateSerial(2000+[num],4,1) AS FromDate, DateSerial(2000+[num]+1,3,31) AS ToDate
FROM numTable
WHERE (2000+[num])<=Year(Date())
ORDER BY [num]

change 2000 to when you want your list to start, modify the WHERE clause where you want it to finish and change the dateserial values for different month/days as required

In your combo, have 3 columns and hide the last two

In a query reference the combo columns 1 and 2

[Micron]

In a query reference the combo columns 1 and 2

As criteria? I thought combo columns were not accessible by queries.

EDIT - A claim I have seen posted here more than once, and prior attempts to do so always raised an error.
However, I just found that it is possible!

[kd2017]

These dates are generally 1st April to 31st March...

If I had to do exactly what you are asking, I'd probably do it this way
Create a table that holds start and end date per range
Create the visible combo that shows Year(StartDate) & "/" Year(EndDate) and the start and end dates as extra columns.
Then when you select an entry, you use those other columns as criteria.

This also allows you to specify other dates, not always 1st April and 31st March.

If that was always the case, then you could just get by with a year column, and create the other values on the fly.?, but I think that would be risky.

Sounds like you need the flexibility to change the range? I like Welsh's plan for a date table.

I might consider instead of storing a [beginning of period] and [end of period] date/time maybe only storing a [beginning of period] date/time and in your queries filter *something* like this

WHERE some_date >= [beginning of period]
  AND some_date < [beginning of NEXT period]

Note that dates in access are stored as floating point numbers. That filter, I think, should prevent inadvertently excluding records in your queries... maybe it's not necessary. I'll hang up and listen