Append Query - Every 7 days

[Ian]

I've created a form with the startdate, number of repitions and day interval.
I'd like to use the informatin to append the data to a table. The
Tbl_Session will hold the individual dates with a uniqueID for each.

Eg. Session1 will start on jan 1, 2009 and run for 5 sessions every 7th day.

Once I grab the data from the form to use in an append query, how do I build
the append query to create the dates in the Tbl_Session?

Jan 1, 09
Jan 8, 09
Jan 15, 09
Jan 22, 09
Jan 29, 09

 

[John W. Vinson]

I've created a form with the startdate, number of repitions and day interval.
I'd like to use the informatin to append the data to a table. The
Tbl_Session will hold the individual dates with a uniqueID for each.

Eg. Session1 will start on jan 1, 2009 and run for 5 sessions every 7th day.

Once I grab the data from the form to use in an append query, how do I build
the append query to create the dates in the Tbl_Session?

Jan 1, 09
Jan 8, 09
Jan 15, 09
Jan 22, 09
Jan 29, 09

It's handy to use an auxiliary table for this purpose: I'll have a table named
Num with one Long Integer field N, filled with values from 0 through 10000 or
so.

Create a query based on Num, with a criterion on N of

< [Number of sessions]

and calculated field

DateAdd("d", [Every how many days?], [Starting date])

Use this as an append query.

 

[Ian]

thx John

I've created a form with the startdate, number of repitions and day interval.
I'd like to use the informatin to append the data to a table. The
Tbl_Session will hold the individual dates with a uniqueID for each.

Eg. Session1 will start on jan 1, 2009 and run for 5 sessions every 7th day.

Once I grab the data from the form to use in an append query, how do I build
the append query to create the dates in the Tbl_Session?

Jan 1, 09
Jan 8, 09
Jan 15, 09
Jan 22, 09
Jan 29, 09

It's handy to use an auxiliary table for this purpose: I'll have a table named
Num with one Long Integer field N, filled with values from 0 through 10000 or
so.

Create a query based on Num, with a criterion on N of

< [Number of sessions]

and calculated field

DateAdd("d", [Every how many days?], [Starting date])

Use this as an append query.

 

[Ian]

Hey John,

Here is the SQL statement:

SELECT Tbl_AuxNum.Val, [forms].[frm_grpsessionnames].[groupsessionname] AS
Name, [forms].[frm_grpsessionnames].[starttime] AS Start,
[forms].[frm_grpsessionnames].[finishtime] AS finish,
(DateAdd("d",([forms].[frm_grpsessionnames].[dayinterval]),([forms].[frm_grpsessionnames].[startdate]))) AS [Date]
FROM Tbl_AuxNum
WHERE (((Tbl_AuxNum.Val)<[forms].[frm_grpsessionnames].[repeatitions]));

but the date is 7 (or whatever number of days after) the startdate for all
entries. Any ideas on how to make it progress?

I've created a form with the startdate, number of repitions and day interval.
I'd like to use the informatin to append the data to a table. The
Tbl_Session will hold the individual dates with a uniqueID for each.

Eg. Session1 will start on jan 1, 2009 and run for 5 sessions every 7th day.

Once I grab the data from the form to use in an append query, how do I build
the append query to create the dates in the Tbl_Session?

Jan 1, 09
Jan 8, 09
Jan 15, 09
Jan 22, 09
Jan 29, 09

It's handy to use an auxiliary table for this purpose: I'll have a table named
Num with one Long Integer field N, filled with values from 0 through 10000 or
so.

Create a query based on Num, with a criterion on N of

< [Number of sessions]

and calculated field

DateAdd("d", [Every how many days?], [Starting date])

Use this as an append query.

 

[John W. Vinson]

but the date is 7 (or whatever number of days after) the startdate for all
entries. Any ideas on how to make it progress?

OOPS! My error, sorry about that. You need to *multiply* the interval by the
value from the num table.

SELECT Tbl_AuxNum.Val, [forms].[frm_grpsessionnames].[groupsessionname] AS
Name, [forms].[frm_grpsessionnames].[starttime] AS Start,
[forms].[frm_grpsessionnames].[finishtime] AS finish,
(DateAdd("d",[forms].[frm_grpsessionnames].[dayinterval]*Tbl_AuxNum.Val,([forms].[frm_grpsessionnames].[startdate])))
AS [Date]
FROM Tbl_AuxNum
WHERE (((Tbl_AuxNum.Val)<[forms].[frm_grpsessionnames].[repeatitions]));

Just fwiw it's spelled "repetitions". Also, DON'T use Date as a fieldname -
it's a reserved word for the builtin SQL Date() function, and will cause
problems.

On a side note if I put the calculated field anywhere except as the last
field the one immediately following it gives an #Error -- do you know why?

Not a clue!

 

[Ian]

worked perfectly!

but the date is 7 (or whatever number of days after) the startdate for all
entries. Any ideas on how to make it progress?

OOPS! My error, sorry about that. You need to *multiply* the interval by the
value from the num table.

SELECT Tbl_AuxNum.Val, [forms].[frm_grpsessionnames].[groupsessionname] AS
Name, [forms].[frm_grpsessionnames].[starttime] AS Start,
[forms].[frm_grpsessionnames].[finishtime] AS finish,
(DateAdd("d",[forms].[frm_grpsessionnames].[dayinterval]*Tbl_AuxNum.Val,([forms].[frm_grpsessionnames].[startdate])))
AS [Date]
FROM Tbl_AuxNum
WHERE (((Tbl_AuxNum.Val)<[forms].[frm_grpsessionnames].[repeatitions]));

Just fwiw it's spelled "repetitions". Also, DON'T use Date as a fieldname -
it's a reserved word for the builtin SQL Date() function, and will cause
problems.

On a side note if I put the calculated field anywhere except as the last
field the one immediately following it gives an #Error -- do you know why?

Not a clue!