Populate a table

[jean]

Hi

I have a table "TblPeriod" containing 1 field "PeriodName" and 60
records.

Format of the field is Date/Time and it is yy-mm

first record is 09-01 second is 09-02 third is 09-03 and so on until
13-12 so it is 60 records for 60 months

I want to be able to type into a text field on a form named "NewDate"
a date value (10-01 as example) and a query will repopulate the table
like 10-01, 10-02, 10-03, 10-04 and so on until 14-12.

How should I proceed ?

thanks

 

[James A. Fortune]

Hi

I have a table "TblPeriod" containing 1 field "PeriodName" and 60
records.

Format of the field is Date/Time and it is yy-mm

first record is 09-01 second is 09-02 third is 09-03 and so on until
13-12 so it is 60 records for 60 months

I want to be able to type into a text field on a form named "NewDate"
a date value (10-01 as example) and a query will repopulate the table
like 10-01, 10-02, 10-03, 10-04 and so on until 14-12.

How should I proceed ?

thanks

If you create tblIntegers as follows:

tblIntegers
ID AutoNumber
I Long
1 1
2 2
3 3
....
61 61

then the following query will produce a list of 60 dates:

qryNewDates:
SELECT tblIntegers.I, DateAdd("m",tblIntegers.I-1,Forms!NewDate!
txtStartDate.Value) AS NewDates FROM tblIntegers WHERE
tblIntegers.I<=60;

Using a value for Forms!NewDate!txtStartDate.Value of 12/1/09, I get:

!qryNewDates:
I NewDates
1 12/1/2009
2 1/1/2010
3 2/1/2010
....
59 10/1/2014
60 11/1/2014

Be sure to resist the urge to coerce the date format prematurely. You
should let the "yy-mm" format of the PeriodName field in TblPeriod do
all the work when the values are updated.

One way to get an update query to match those values is to use the ID
field for TblPeriod (PID?). Air SQL:

UPDATE qryNewDates, TblPeriod SET TblPeriod.PeriodName =
qryNewDates.NewDates WHERE TblPeriod.PID = qryNewDates.I;

Suppose you don't want to re-create the primary key and there is an
offset (say 188) corresponding to a first NewDate at PID = 189 and the
PID's are contiguous:

UPDATE qryNewDates, TblPeriod SET TblPeriod.PeriodName =
qryNewDates.NewDates WHERE TblPeriod.PID = qryNewDates.I + 188;

Before running the query, I would check (usually with code behind the
form's command button) that a valid date is contained in txtStartDate.

J. A. Fortune
(e-mail address removed)

 

[jean]

If you create tblIntegers as follows:

tblIntegers
ID AutoNumber
I Long
1 1
2 2
3 3
...
61 61

then the following query will produce a list of 60 dates:

qryNewDates:
SELECT tblIntegers.I, DateAdd("m",tblIntegers.I-1,Forms!NewDate!
txtStartDate.Value) AS NewDates FROM tblIntegers WHERE
tblIntegers.I<=60;

Using a value for Forms!NewDate!txtStartDate.Value of 12/1/09, I get:

!qryNewDates:
I NewDates
1 12/1/2009
2 1/1/2010
3 2/1/2010
...
59 10/1/2014
60 11/1/2014

Be sure to resist the urge to coerce the date format prematurely.  You
should let the "yy-mm" format of the PeriodName field in TblPeriod do
all the work when the values are updated.

One way to get an update query to match those values is to use the ID
field for TblPeriod (PID?).  Air SQL:

UPDATE qryNewDates, TblPeriod SET TblPeriod.PeriodName =
qryNewDates.NewDates WHERE TblPeriod.PID = qryNewDates.I;

Suppose you don't want to re-create the primary key and there is an
offset (say 188) corresponding to a first NewDate at PID = 189 and the
PID's are contiguous:

UPDATE qryNewDates, TblPeriod SET TblPeriod.PeriodName =
qryNewDates.NewDates WHERE TblPeriod.PID = qryNewDates.I + 188;

Before running the query, I would check (usually with code behind the
form's command button) that a valid date is contained in txtStartDate.

J. A. Fortune
(e-mail address removed)- Hide quoted text -

- Show quoted text -

Thanks a lot
Works perfectly