CODEWITHSUNDEEP
sqlms-accessvba
Active_t
Active_t

Reputation: 177

Create a list of months between two dates in Access

I'm doing things with Microsoft Access. Now i want to make a list of dates between two dates.

for example input : 1-1-2010 and 5-1-2010 give me the values.

1-1-2010
2-1-2010
3-1-2010
4-1-2010
5-1-2010

I want to do this in SQL if it's possible, otherwise in VBA.

Upvotes: 2

Views: 6018

Answers (2)

mwolfe02
mwolfe02

Reputation: 24207

Add a local table named YrMos using the following code, adjusting the Start/End years as necessary (note: I'm using RunSQL here only because it is DAO/ADO agnostic; there are better alternatives specific to DAO and ADO):

Sub CreateYrMos()
Const StartYear = 1950
Const EndYear = 2050
Dim Y As Integer, M As Integer

    DoCmd.SetWarnings False
    DoCmd.RunSQL "CREATE TABLE YrMos " & _
                 "(MoStart DATE CONSTRAINT MoStartIndex PRIMARY KEY, " & _
                 " MoEnd Date CONSTRAINT MoEndIndex UNIQUE, " & _
                 " Yr Integer, Mo Integer, DaysInMo Integer, " & _
                 " CONSTRAINT YrMoIndex UNIQUE (Yr, Mo))"
    For Y = StartYear To EndYear
        For M = 1 To 12
            DoCmd.RunSQL "INSERT INTO YrMos (MoStart, MoEnd, Yr, Mo, DaysInMo) " & _
                         "VALUES (#" & DateSerial(Y, M, 1) & "#, #" & DateSerial(Y, M + 1, 0) & "#, " & Y & ", " & M & ", " & Day(DateSerial(Y, M + 1, 0)) & ")"
        Next M
    Next Y
    DoCmd.SetWarnings True
End Sub

Once you've created the table using the above code, the actual query becomes trivial:

SELECT YrMos.*
FROM YrMos
WHERE MoStart BETWEEN #1/1/2010# AND #5/1/2010#

I keep a local copy of this table (with an appropriate range of years for my needs) within several of my access applications. I've found it to be a much more efficient and practical approach than other more 'elegant' solutions.

Upvotes: 3

Vinnie
Vinnie

Reputation: 3929

Is your format mm-dd-yyyy or dd-mm-yyyy?

Edit:

Based on your comment and info, here is how I accomplished it in T-SQL

The setup:

-- drop table dates 
create table dates (value datetime)

insert into dates
select '1-1-2010'
union select '5-1-2010'

And the WHILE loop:

declare @output table (listDates datetime)
declare @maxDate datetime
declare @minDate datetime

set @maxDate = (select MAX(value) from dates)
set @minDate = (select MIN(value) from dates)

while @minDate <= @maxDate
    begin
     insert into @output select @minDate
     set @minDate = DATEADD(mm,1,@minDate)
    end 

select * from @output

Which gave me the output

listDates
-----------------------
2010-01-01 00:00:00.000
2010-02-01 00:00:00.000
2010-03-01 00:00:00.000
2010-04-01 00:00:00.000
2010-05-01 00:00:00.000

The format was import because the DATEADD function needs to know if you're going to add months, days, years, etc. to the value. So, if you wanted to add days, you could change mm to dd which gives you a much longer list.

Hope this helps you out.

Upvotes: 0

Related Questions