Creating a list of dates from a date range

[logopolis]

Is it possible to create a list of dates in a query from a start and end date in a table. eg I have a start date of 1st Jan 2006 and and end date of 30th Jan 2006 and it lists all the dates in between as a new record.

John

 

[SkipVought]

Hi,

Have a DUMMY table with a column with values from 1 to 30.

Join with the dummy table.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[fneily]

Do you want every date to be a field in the record? Hope not, that's just bad design.

Do you want a straight list of dates separated by a , or ;? then you could something like: [Orderdate] is start date
Dim i As Integer
Dim holdint As Integer
holdint = Me![Text11] 'textbox is an integer for # of days
For i = 1 To holdint
holddate = holddate & DateAdd("d", i, [Orderdate]) & ";"
Next
Me![Text16].Value = holddate 'holds answer

Could you be more specific.

 

[logopolis]

Hi sorry I was a little vague. There is a table of term dates for a school, Start, end and half term start and end. The database enters in two sorts of booking, one would be for a one off booking eg. an appointment. and then there would be a repeadted booking eg. period 3 on Tuesday. It is for the second type of booking I need the list of dates, as I need to be able to run a report for specific dates, so on a specific Tuesday you would run a report and that period three would show, but these repeating dates must only occur in term time.