dateadd looping

[Ravenray]

Is there a way to do a dateadd looping? Here's the situations. I have a date of 10/1/03 and want to create a query that will loop it according to current date. I know that if I was using dateadd("m", 2, #10/1/03#) will generate 11/1/03. So in my query it would show something like this.

10/1/03 | 11/1/03

But what I really want is for instatance that the difference between 10/1/03 and date() it should spit out like this.

10/1/03 | 11/1/03 | 12/01/03

Any help will be appreciated. Thank You.

 

[raskew]

Don't have a clue why you'd want to do what you're asking. Nonetheless, here's a function that will do it for you.

Function DteAddem(pdate As Date) As String
'*******************************************
're:        [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=58828[/url]
'Purpose:   Create string displaying first day of
'           each month between pdate and date()
'Author:    R Askew
'Inputs:    ? DteAddem(#8/15/03#) 'today is 12/9/03
'Output:    8/1/03 | 9/1/03 | 10/1/03 | 11/1/03 | 12/1/03
'*******************************************

Dim datehold As Date, n As Integer
Dim tDate As Date, strHold As String

tDate = DateSerial(Year(Date), Month(Date), 1) 'first day of current month
datehold = DateSerial(Year(pdate), Month(pdate), 1) 'first day of input month

strHold = IIf(datehold < tDate, CDate(datehold) & " | ", "")

If datehold < tDate Then
   Do While datehold < DateAdd("m", -1, tDate)
      datehold = DateAdd("m", 1, datehold)
      strHold = strHold & datehold & " | "
   Loop
End If

DteAddem = strHold & CDate(tDate)
   
End Function

Put it in a query that includes dates [xdate] like this:

SELECT
    Table15.xdate
  , dteAddem([xdate]) AS MySpan
FROM
   Table15;

HTH - Bob

p.s.: Please post back as to what it is you're doing.

 

[Ravenray]

I wanted to created a payment due date just for looks.