Date Array Using Access SQL

Is there a way to create a SQL that can return list/array of dates between two dates?

I want to do this using Access SQL.

Thanks
Ray TurnerSenior ConsultantAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Yes. Create a query like this:

SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca;

Open in new window

Save it as qdyFactor. Then create this query:

SELECT DISTINCT 
    DateAdd("d",[Factor],[DateFrom]) AS Dates
FROM 
    qdyFactor
WHERE 
    qdyFactor.Factor Between 0 And DateDiff("d",[DateFrom],[DateTo]);

Open in new window

This will create the list of dates.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Ray,

yes. You can create a Numbers table with a record for each number like 0, 1,2,3, etc. -- however many numbers you will need for your date range.

Numberz
- Num, long, primary key

 Then make a query on top of it like this:
field --> Num
criteria --> between 0 and 30 'or whatever criteria you want
field --> MyDate: [Num] + #1/1/2018#

have an awesome day,
crystal
0
 
Ray TurnerSenior ConsultantAuthor Commented:
I need 42 dates.  I would like to pass a starting date to the query.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
creative way to get a list of numbers, Gustav ~

Ray, A numbers table is useful for other things as well, like getting multiple copies of a report. I often have records 0 to 365 and then for 2 years, you can use 2 copies of the numbers table; one for the day and one for the year. Or your numbers table could have more records too. Here is some code to create the records for Numberz once you make the table:
Sub CreateNumbersRecords()
 'strive4peace

   ' click HERE
   ' press F5 to Run

   'NEEDS REFERENCE to
   'Microsoft DAO Object Library
    
   ' tablename --> Numberz
   ' fieldname --> Num, long, primary key
   
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , rs As DAO.Recordset
      
   Dim lngStart As Long _
      , lngStop As Long _
      , i As Long
      
   ' every now and then, you might need to run this again
   ' to change the max number created
   lngStop = 365
      
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Numberz", dbOpenDynaset)
   
   'figure out the last number in the table
   'and create numbers from there
   
   lngStart = Nz(DMax("Num", "Numbers"), 0) + 1
   
   For i = lngStart To lngStop
      rs.AddNew
      rs!Num = i
      rs.Update
   Next i
   
   MsgBox "Done creating records in Numbers"
 
Proc_Exit:
   On Error Resume Next
   If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
   End If
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   CreateNumbersRecords"

   Resume Proc_Exit
   Resume
End Sub

Open in new window

If you want 42 dates, add Num to the date you want to start with and get from 0 to 41 in Numberz.

have an awesome day,
crystal
0
 
Gustav BrockConnect With a Mentor CIOCommented:
I need 42 dates.  I would like to pass a starting date to the query.

Well, that was not what you asked for.
But it is easily done:

SELECT DISTINCT 
    DateAdd("d",[Factor],[DateFrom]) AS Dates
FROM 
    qdyFactor
WHERE 
    qdyFactor.Factor Between 0 And 42;

Open in new window

0
 
aikimarkCommented:
If you need help with tally/number tables, read my article on the subject.  I include date generation as one of my examples.
https://www.experts-exchange.com/articles/5410/Creating-and-Using-a-Tally-Table-in-Access.html
0
 
BitsqueezerCommented:
Hi,

beside the fact that of course a number table is always useful, I would simply create a calendar table and fill it with the dates of the next 100 years for example. This avoids the need to calculate any date value so you get the best SQL performance using it. Beside a simple date value you can also use additional fields like a Weekday as value or as name, a "weekend" bit field, a "workday" bit field, single fields for day, month and year values and so on - whatever you find useful.
With such a calendar table you can create very fast and simple SQL queries which also often avoids the need to use date functions to calculate something.

For your question it would then be as simple as (having "F_Date" as the name of the date value in the calendar table and "tblCalendar" as the table name):

SELECT F_Date
FROM tblCalendar
WHERE F_Date BETWEEN <Your Start Date> AND <Your End Date>

Open in new window


or

SELECT F_Date
FROM tblCalendar
WHERE F_Date >= <Your Start Date> AND F_Date <= <Your Start Date> + 42

Open in new window



Of course there would be a unique index on the "F_Date" field or it is the primary key.

Cheers,

Christian
0
 
Ray TurnerSenior ConsultantAuthor Commented:
Thanks!  Very clever solution.
0
 
Gustav BrockCIOCommented:
You are welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.