Query with no table

[TastyWheat]

I want to make a table that is dynamic (for use in a combo box). Basically I want the table/query to display the last 5 two-week periods. It is every two weeks from a specific day though. I'm not pulling any data from a table, I just need a column of dates. So I would want my table to look like this:

Period
------
2/28/2006 - 3/13/2006
2/14/2006 - 2/27/2006
...
...
...

Can I do this without using code? I know it's pretty straightforward to make a loop to get these dates but I just feel that's a messier solution.

 

[raskew]

Hi -

Afraid there's going to have to be some code somewhere, or one heck of a messy query.

Here's a function that will create a string for use as a value list in your list or combo box. Paste it into a new module and call it from the debug (immediate) window with:

? LastFive(#2/28/06#)

Function LastFive(pStart As Date) As String
Dim dteHold As Date
Dim strHold As String
Dim n As Integer

    dteHold = DateAdd("d", -56, pStart)
    strHold = ""
    For n = 1 To 5
      strHold = strHold & dteHold & " - " & DateAdd("d", 13, dteHold) & "; "
      dteHold = DateAdd("d", 14, dteHold)
    Next n
    LastFive = Left(Trim(strHold), Len(Trim(strHold)) - 1)
       
End Function

HTH - Bob