Dates

[UT227]

Is there a way to calculate the dates between 2 dates? If I have beginning date 2/1/2018 and ending date 2/5/2018, I would like to see all the dates 2/1, 2/2, 2/3/, 2/4, 2/5. Is this possible?

[kd2017]

How about generating a calendar table and just SELECT [date field] FROM [calendar table] WHERE [date field] BETWEEN [start date] AND [end date] ?

[Minty]

It depends what you want the for afterwards?
There are arguments for and against creating a calendar table, as in theory almost everything involving dates can be calculated.

If it's to display a calendar type control then you can simply create the dates into an array.

[CJ_London]

Also depends on why you need this.

The only way is either to create a calendar table covering the whole period that is likely to be required which you can use in your (dao) queries, or to generate a table between two specific dates when required. The latter might be a temporary (dao) table unique to the user or you might create an in memory recordset (using ado) to assign to a form or report recordset

[UT227]

I'm making a sign in sheet for training. I have a schedule table which gives the beginning and ending dates of the class. On the sign in sheet, I need each day listed so the student can initial his attendance.

[UT227]

I've never made a calendar table. What is involved?

[CJ_London]

I've never made a calendar table. What is involved?

create a new table with a single field called say calDate of datetype - you can make it the primary key

then populate it with all the days of the year - 365 records per year (plus 1 for leap years)


you can either populate manually or use a bit of vba code - something like

Dim d As Date
For d = Date To Date + 365 Step 1
    CurrentDb.Execute "INSERT INTO tblCalendar (calDate) Values(#" & Format(d, "mm/dd/yyyy") & "#)"
Next d

[UT227]

Code sounds better. Being that this is my first time doing this. Where do I put the code?

[CJ_London]

wherever you like - to run as is, put in a function in a general module, then run the function. Or you could put it on a click event on a button on a form.

As written, you would only run it once, and calDate being a primary key, running it again will not add the records again. But you could use a form, with start and end date controls and then modify the code to reference these controls rather than using the date function.

[UT227]

That was pretty cool. Now, I have a year between today and 1 year away. Now, I have more questions on it. How do I get the dates into my report with it? Also, What happens after next year? Do I delete it and run it again or do I delete it after its use and rerun it each time I need it? This is a new one for me.

[CJ_London]

How do I get the dates into my report with it?

left join it to your report recordsouce

Also, What happens after next year? Do I delete it and run it again or do I delete it after its use and rerun it each time I need it?

entirely up to you all depends on what you are doing, but personally I would just add the following year, month, whatever as required

[ArviLaanemets]

Simplest way to handle this Calendary table is just to insert dates for p.e. 10 - 20 (or 50 - 100 depending on your perspective) years in advance (and probably for as much into past too), so you haven't add new dates too often.
And whenever you need such table in some another application, you can use import-export to get the same table into another app.

Another advice - you can add some other columns like DateYear (in format yyyy), DateMonth (in format yyyymm), DateWeek (in format yyyyww), IsWorkday (in format boolean), IsHoliday (in format boolean). It will be handy in some calculations with dates (maybe not currently, but in future for sure), where sometimes you have to use complex formulas otherwise.

[UT227]

Thanks for all the advice. I think I have an idea on how to put them on my report. I have a Date Calendar. It works great. How do I get the dates from between the beginning date and the ending date? I need to list each date including the beginning and ending.

[CJ_London]

use a criteria of between your beginning and end dates on the calendar date

[ArviLaanemets]

To elaborate Ajax's response, you create a query like

SELECT CalDate FROM tblCalendary WHERE CalDate BETWEEN StartDate AND EndDate

This will be a base for a query you report will be based on. Now you continue with joining other tables to get data for report, and when all returned data are OK, you design a report based on this query.