Inserting Records

[Bre-x]

Hi,

I have two pieces of information Start_Date and End_Date

I would like to populate a table with each single date between these values.

Values: 07/10/2007, 7/13/2007

Table:

ID, Mydate
1 07/10/2007
2 07/11/2007
3 07/12/2007
4 07/13/2007


Regards,

Bre-x

 

[Guest]

Try this using a table name CountNumber with a field named CountNUM filled
with 0 (zero) to your maximum date spread.
SELECT DateAdd("d",[CountNUM],[Start_Date]) AS Your_Fill_Date INTO
YourDateTable
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=DateDiff("d",[Start_Date],[End_Date])));

 

[Bre-x]

I didnt explain myself very well

The Start_Date and End_Date will be enter by the user, then I would like to
populate the values into my "empy" table.

Thank you

Regards,

Bre-x

 

[Guest]

Just change my posted query from a 'Make-table' to an 'Append' query.

 

[Bre-x]

INSERT INTO CountNumber ( CountNUM )
SELECT DateAdd("d",[CountNUM],"07/02/07") AS Your_Fill_Date
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=DateDiff("d","07/02/07","07/05/07")));

like that?

sorry I am having a off day



Bre-x

 

[Guest]

Like this ---
INSERT INTO YourDateTable ( Your_Fill_Date )
SELECT DateAdd("d",[CountNUM],[Start_Date]) AS Your_Fill_Date
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=DateDiff("d",[Start_Date],[End_Date])));

--
KARL DEWEY
Build a little - Test a little

INSERT INTO CountNumber ( CountNUM )
SELECT DateAdd("d",[CountNUM],"07/02/07") AS Your_Fill_Date
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=DateDiff("d","07/02/07","07/05/07")));

like that?

sorry I am having a off day



Bre-x

 

[Jamie Collins]

INSERT INTO CountNumber ( CountNUM )
SELECT DateAdd("d",[CountNUM],"07/02/07") AS Your_Fill_Date
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=DateDiff("d","07/02/07","07/05/07")));

sorry I am having a off day

I think what you may have missed is that KARL is proposing you first
create an auxiliary table of unique integers, a standard trick in SQL,
named CountNumber.

Speaking of auxiliary tables, I'm wondering why you are loading an
empty table. Perhaps you could use a Calendar auxiliary table to
achieve the same goal. See:

"Why should I consider using an auxiliary calendar table?"
http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Jamie.

--