If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Generation.
Hi,
Probably a numpty question but ..... I need a query to generate a list of dates of every date in the year. Is there a way to do this that is independent of a dataset or do I need to create a table in excel and export? Regards. Bill. |
#2
|
|||
|
|||
Date Generation.
I don't think you can do this in a query, but you can certainly do it in
code. The solution will depend on what you want to do with the 'list'. Depending on your requirements, you might need an array in memory, or you might need to store the data in a table, or you might need something else. Here's an example that first creates the dates in an array, then writes the array to a table ... Public Sub DatesInYear() Dim adtmDates() As Date Dim dtmDate As Date Dim lngDay As Long Dim strSQL As String Dim rst As ADODB.Recordset Const strcUSDate As String = "mm/dd/yyyy" On Error Resume Next CurrentProject.Connection.Execute "DROP TABLE DateTable" On Error GoTo 0 If IsDate("29 February " & CStr(Year(Date))) Then ReDim adtmDates(365) Else ReDim adtmDates(364) End If For dtmDate = DateSerial(Year(Date), 1, 1) To DateSerial(Year(Date) + 1, 1, 0) adtmDates(lngDay) = dtmDate lngDay = lngDay + 1 Next dtmDate strSQL = "CREATE TABLE DateTable (DateField DATETIME)" CurrentProject.Connection.Execute strSQL For lngDay = LBound(adtmDates) To UBound(adtmDates) strSQL = "INSERT INTO DateTable (DateField) VALUES (#" & _ Format$(adtmDates(lngDay), strcUSDate) & "#)" CurrentProject.Connection.Execute strSQL Next lngDay DoCmd.OpenTable "DateTable" End Sub -- Brendan Reynolds Access MVP "Bill" wrote in message ... Hi, Probably a numpty question but ..... I need a query to generate a list of dates of every date in the year. Is there a way to do this that is independent of a dataset or do I need to create a table in excel and export? Regards. Bill. |
#3
|
|||
|
|||
Date Generation.
At a minimum you would need a table of integers with the numbers from 1 to
366 SELECT DateSerial ([Input Year],1,IntegerField) as EveryDay FROM IntegerTable WHERE Year(DateSerial ([Input Year],1,IntegerField)) = [Input Year] "Bill" wrote in message ... Hi, Probably a numpty question but ..... I need a query to generate a list of dates of every date in the year. Is there a way to do this that is independent of a dataset or do I need to create a table in excel and export? Regards. Bill. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding info to query | mjj4golf | Running & Setting Up Queries | 20 | January 9th, 2006 02:29 AM |
Reports with Date Range - Errors | jwr | Setting Up & Running Reports | 12 | August 8th, 2005 12:57 PM |
Revised Date Question | Spectra | Running & Setting Up Queries | 0 | March 18th, 2005 12:19 PM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
more dates!!! | brigid | Running & Setting Up Queries | 6 | May 26th, 2004 10:59 AM |