Solved How to create a query that contains all dates?

K

KitaYama

Well-known member
Local time
Today, 15:07
Joined
Jan 6, 2022
Messages
2,223
I have a tblHolidays that lists only holidays of each year :

HolidayPKHolidayRemarks
16532024/05/02Elders Day
16542024/05/24Sport Day
16552024/05/28Culture Day

Is there any way to use this table to create a query that contains all dates (Not only holidays)
Something like :

dDateRemarks
----
2024/04/29Ocean Day
2024/04/30
2024/05/01
2024/05/02Elders Day
2024/05/03
----
2024/05/24Sport Day
----
2024/05/28Culture Day
----


I've used --- to shorten the list. The query should contain all the dates between.
Thanks.
 
Last edited:
Build a table of dates. I use something like this and store additional fields (most are unnecessary) so I do not have to always calculate them on the fly.
Code: 
Public Sub FillDates()
  'Edit this to fill table
  Const tableName = "tblDates"
  Const StartDate = #1/1/2021#
  Const EndDate = #12/31/2030#

  Dim IterativeDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim DayOfWeek As String
  Dim blnHoliday As Boolean

  IterativeDate = StartDate
  Do While IterativeDate < EndDate
    strDate = SQLDate(IterativeDate)
    DayOfWeek = WeekdayName(Weekday(IterativeDate))
    DayOfWeek = sqlTxt(DayOfWeek)
    blnHoliday = IsHoliday(IterativeDate)
    'field names need to match your table
    strSql = "Insert into " & tableName & " (dtmDate, LongDayOfWeek, IsHoliday, SqlDate) values (" & strDate & ", " & DayOfWeek & ", " & blnHoliday & ", " & SQLDate(IterativeDate) & ")"
    IterativeDate = IterativeDate + 1
    'Debug.Print strSql
    CurrentDb.Execute strSql
  Loop

End Sub

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
/CODE]

tblDates [B]tblDates[/B]

[TABLE]
[TR]
[TH]ID[/TH]
[TH]dtmDate[/TH]
[TH]ShortDayOfWeek[/TH]
[TH]LongDayOfWeek[/TH]
[TH]DayOfWeekNumber[/TH]
[TH]ShortMonth[/TH]
[TH]LongMonth[/TH]
[TH]MonthNumber[/TH]
[TH]YearNumber[/TH]
[TH]isHoliday[/TH]
[TH]IsWorkDay[/TH]
[TH]SqlDate[/TH]
[/TR]
[TR]
[TD][RIGHT]4682[/RIGHT][/TD]

[TD][RIGHT]1/1/2021[/RIGHT][/TD]

[TD]1[/TD]
[TD]01[/TD]

[TD][RIGHT]6[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]True[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/1/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4683[/RIGHT][/TD]

[TD][RIGHT]1/2/2021[/RIGHT][/TD]

[TD]2[/TD]
[TD]02[/TD]

[TD][RIGHT]7[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/2/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4684[/RIGHT][/TD]

[TD][RIGHT]1/3/2021[/RIGHT][/TD]

[TD]3[/TD]
[TD]03[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/3/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4685[/RIGHT][/TD]

[TD][RIGHT]1/4/2021[/RIGHT][/TD]

[TD]4[/TD]
[TD]04[/TD]

[TD][RIGHT]2[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/4/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4686[/RIGHT][/TD]

[TD][RIGHT]1/5/2021[/RIGHT][/TD]

[TD]5[/TD]
[TD]05[/TD]

[TD][RIGHT]3[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/5/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4687[/RIGHT][/TD]

[TD][RIGHT]1/6/2021[/RIGHT][/TD]

[TD]6[/TD]
[TD]06[/TD]

[TD][RIGHT]4[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/6/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4688[/RIGHT][/TD]

[TD][RIGHT]1/7/2021[/RIGHT][/TD]

[TD]7[/TD]
[TD]07[/TD]

[TD][RIGHT]5[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/7/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4689[/RIGHT][/TD]

[TD][RIGHT]1/8/2021[/RIGHT][/TD]

[TD]8[/TD]
[TD]08[/TD]

[TD][RIGHT]6[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/8/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4690[/RIGHT][/TD]

[TD][RIGHT]1/9/2021[/RIGHT][/TD]

[TD]9[/TD]
[TD]09[/TD]

[TD][RIGHT]7[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/9/2021[/TD]
[/TR]
[TR]
[TD][RIGHT]4691[/RIGHT][/TD]

[TD][RIGHT]1/10/2021[/RIGHT][/TD]

[TD]10[/TD]
[TD]10[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD]Jan[/TD]
[TD]January[/TD]

[TD][RIGHT]1[/RIGHT][/TD]

[TD][RIGHT]2021[/RIGHT][/TD]

[TD][RIGHT]False[/RIGHT][/TD]

[TD][RIGHT]No[/RIGHT][/TD]

[TD]1/10/2021[/TD]
[/TR]
[/TABLE]
 
@MajP Thanks for taking your time and trying to help.
  • By your suggestion, Am I correct if I assume I can't have a query to do what I need and I must use a full dates table?
  • It seems that you copied the code from a web page. Your suggested code contains a lot of [TD] & [TR] that as far as I'm aware, are used in HTML. You may want to delete it in case anyone visit this page in future.
  • There's a IsHoliday function there. I wrote my own function to retrieve holidays from current table. You may want to add yours to prevent error. (for future visitors)
  • Your strSql has a LongDayOfWeek field that suggests the table field is Number data type, but it's trying to add the actual name of the days as string.
Thanks again for your help.

Edit : Case solved. I used a variation of your code to add the missing dates to the table and then sorted it by date. Thanks.
 
Last edited:
KitaYama said:
Is there any way to use this table to create a query that contains all dates (Not only holidays)
Click to expand...
Here's an example of how I generated consecutive days using a Cartesian Query. You'll have to decide whether the speed of calculating dates is worth it as compared to creating a table with all the dates you need.
thedbguy.blogspot.com

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...
thedbguy.blogspot.com thedbguy.blogspot.com
 
theDBguy said:
Here's an example of how I generated consecutive days using a Cartesian Query. You'll have to decide whether the speed of calculating dates is worth it as compared to creating a table with all the dates you need.
thedbguy.blogspot.com

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...
thedbguy.blogspot.com thedbguy.blogspot.com
Click to expand...
I think I will go for a full date table as @MajP suggested.
I actually wanted to use the query in a custom calendar to show it faster. (Preventing repeated DLookup functions for holidays on each month)

Thanks
 
Last edited:
KitaYama said:
I thinks I will go for a full date table as @MajP suggested.
I actually wanted to use the query in a custom calendar to show it faster. (Preventing repeated DLookup functions for holidays on each month)

Thanks
Click to expand...
Sounds like a plan. Good luck!
 
I use a small 'counter' table I call usysCounter
1716892248597.png


and a 'counter' query (I call usysCount) - this version counts from 0 to 99,999 - takes <second to display 100,000 rows

SQL: 
SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)+([tenthousands].[num]*10000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands, usysCounter AS tenthousands;

which I can then apply to a date - for example

SQL: 
SELECT CDate([counter]) AS Expr1
FROM usysCount
WHERE CDate([counter]) Between #1/1/2020# And #12/31/2025#

or

SQL: 
SELECT CDate([counter]) AS Expr1
FROM usysCount
WHERE Counter Between 40000 And 45000

plenty of other variations you can use - this one provides dates for the first couple of centuries in the last millenium
SQL: 
SELECT CDate(#1/1/1000#+[counter]) AS Expr1
FROM usysCount;

left join it to your holiday table

It's the method I use for my 'forever scrolling' calendar, based on the outlook calendar style. The dates with events are displayed bold and with a colour depending on type of 'event' e.g. public holiday, weekend, or in the case of personnel for example, personal holiday, illness, long term leave, etc. Hovering over such a formatted date displays the detail or clicking on a date opens an edit form
 
Last edited:
CJ_London said:
I use a small 'counter' table
View attachment 114274

and a 'counter' query - this version counts from 0 to 99,999 - takes <second to display 100,000 rows

SQL: 
SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)+([tenthousands].[num]*10000)) AS [Counter]
FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands, usysCounter AS tenthousands;

which I can then apply to a date - for example

SQL: 
SELECT CDate([counter]) AS Expr1
FROM usysCount
WHERE CDate([counter]) Between #1/1/2020# And #12/31/2025#

or

SQL: 
SELECT CDate([counter]) AS Expr1
FROM usysCount
WHERE Counter Between 40000 And 45000

plenty of other variations you can use - this one provides dates for the first couple of centuries in the last millenium
SQL: 
SELECT CDate(#1/1/1000#+[counter]) AS Expr1
FROM usysCount;

left join it to your holiday table

It's the method I use for my 'forever scrolling' calendar, based on the outlook calendar style. The dates with events are displayed bold and with a colour depending on type of 'event' e.g. public holiday, weekend, or in the case of personnel for example, personal holiday, illness, long term leave, etc. Hovering over such a formatted date displays the detail or clicking on a date opens an edit form
Click to expand...
I'll give it try as soon as I'm back to my desk.
Thanks for the details.
 
@CJ_London I tested your solution and it's absolutely perfect.
Since your suggestion was air code, you may want to correct them for others who may visit this thread in future.
They should be :
SQL: 
SELECT CDate([counter]) AS Expr1
FROM Counter
WHERE CDate([counter]) Between #1/1/2020# And #12/31/2025#

and

SQL: 
SELECT CDate([counter]) AS Expr1
FROM Counter
WHERE CDate([counter]) Between 40000 And 45000

and

SQL: 
SELECT CDate(#1/1/1000#+[counter]) AS Expr1
FROM Counter;

Again, Million thanks for your time and help.
It was perfect.
 
KitaYama said:
Since your suggestion was air code
Click to expand...
It wasn’t actually 😊

table is called usysCounter and the query usysCount which you have renamed as Counter (same as the field name)

and the second sql, you are using cDate in the criteria when it is not necessary.

However I have modified my explanation to be clear about what is called what:giggle:

Thanks for the clarification, sometimes I rush things
 
KitaYama said:
I think I will go for a full date table as @MajP suggested.
Click to expand...
I've used the full date table but you can also take advantage of the way dates are actually stored - 12/30/1899 = 0 and increment by 1 for each subsequent day or decrement by 1 for each previous day so 12/31/1899 = 1 and 12/29/1899 = -1

Given that, you can create a table with the values of 1 - n depending on how many days you want in your result set. Then using a start date, add the start date to the number to get each date. If you want dates earlier than your start date then include negative values.
 
Hi,
Would it be possible to scroll through this counter, perform a series of actions and then when complete scroll to next date and perform same actions again?
 
The counter is just a range of numbers - you can reset the range as required
 
CJ_London said:
SQL: 
SELECT CDate(#1/1/1000#+[counter]) AS Expr1
FROM usysCount;

left join it to your holiday table
Click to expand...
A calendar table only develops its real charm when the relevant fields are indexed (date value unique, additional fields with date formats simply indexed) so that these indexes can be used to improve performance in JOINs, filters and groupings.
Providing the date values as in the query shown does not yet offer this. The step towards a table should therefore still be taken, especially since a planned calendar table would only have to be created once, but can subsequently be used in a wide variety of ways.
 
ebs17 said:
when the relevant fields are indexed
Click to expand...
Agree that particular value is not indexed as it is effectively a criteria - the field it would be linked to would be (or should be) indexed

I have used this method for many years - typically only returning a few hundred records, performance has not been an issue since records are returned as close to instantaneous as makes no difference.

by all means, create 100,000 indexed dates (or more) to cater for all eventualities. It might be more charming but for 99% of situations no discernible improved performance.
 
You must log in or register to reply here.

Similar threads

P
VBA query code to select a date between a start and finished date
Replies
5
Views
926
cheekybuddha
cheekybuddha
K
Solved How to get Min of two fields of a table/query?
2
Replies
25
Views
3,184
KitaYama
K
K
Query to to exclude records with a date within last 4 months, and show records where most recent date is older than that
Replies
9
Views
1,224
kevin_maher
K
A
Solved How to change date portion at beginning
Replies
13
Views
1,807
Ashfaque
A
H
How to give a zero value to count if zero
Replies
6
Views
1,637
Harris@Z
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom