create a list of dates using sql query

[learning_grexcel]

Hi,
Every time when I want a list of date in a column of access table. I do it in excel and then copy and paste in access. Is there anyway i write just a query and it produces a list of date for a month say for May, 2012?

 

[SydneyGeek]

I tend to cheat. Build a table of dates going 20 years into the future. Add columns for year, month, week, weekday financial year / half year / quarter and anything else you can think of. (Use Excel to build the table and import it). Avoid using Day, Month, Week or Year as field names; Access will get confused because those words are function names. Because I call the table AllTime I tend to use AT_Date, AT_Month, etc as the field names.
Then just query that table any time you need a date range. For example, you can easily eatract all the Mondays in May 2012 with a couple of criteria.
Transfer that table between your databases as needed.

Denis

 

[james_lankford]

not a query, but easy enough with VBA

'****************************************
Sub insert_some_dates()
    
    DoCmd.SetWarnings False
    
    Dim sql As String
    
    Dim start_date As Date
    Dim end_date As Date
    Dim current_date As Date
    
    start_date = #5/1/2012#
    end_date = #6/1/2012#
    
    current_date = start_date
    
    Do While current_date < end_date
        
        sql = "insert into mydate_table (mydate_column) values ( #" & current_date & "# )"
        Debug.Print sql
        DoCmd.RunSQL sql
        current_date = current_date + 1
        
    Loop
        
    DoCmd.SetWarnings true
    
End Sub
'****************************************

 

[learning_grexcel]

wow! It works like charm.
But I'm wondering if there is any simple sql query to write the same so that I do it myself. Or is there any way I can start learning writing such vba codes? I wonder how to start with.

@SydneyGeek

Your advise looks good but in my case when I try to import any of my tables from export to access, I get error. I usually just copy and paste and it takes lot of time because usually my data is more than 2,00,000.

 

[Norie]

20 years is only 7000+ odd days, which equates to 7000+records.

That shouldn't be a problem importing form Excel.

Obviously you could have a more complicated 'date' table.

 

[learning_grexcel]

Yes I can do the dates column, but I was just talking about other tables in my database.

 

[Norie]

Isn't that a different question?

 

[weav8060]

Hi,
Every time when I want a list of date in a column of access table. I do it in excel and then copy and paste in access. Is there anyway i write just a query and it produces a list of date for a month say for May, 2012?

My simple solution, eg. to list last three weeks' dates. I use 'recursive queries" (probably wrong term for it) a lot to generate master lists.

1. create table 'tbl days' containing one number field only, "days" = 1,2,...20,21
2. create query working on two copies of 'tbl days' as follows:

SELECT DateValue(Now())-[tbl days_1]![days] AS calendar_date
FROM [tbl days], [tbl days] AS [tbl days_1]
WHERE (((DateValue(Now())-[tbl days_1]![days])<=[tbl days]![days]))
GROUP BY DateValue(Now())-[tbl days_1]![days]

 

[weav8060]

(deleted)

 

[xenou]

I think that writing a "simple" sql query to generate dates is non-trivial because of leap years. So best to follow SydneyGeek's advice.