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.

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.

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.