Loop

[TIZIANA VENTURINI]

I must generate a maintenance plan.
The system has many components who have at intervals various need of
maintenance.
I have a table that contains the start date , the end date and the
interval between every maintenace like this:

idcompo startdate enddate day
CA1 03/08/2004 06/15/2004 7
BR2 03/08/2004 06/15/2004 15

etc...

I write this code:

Dim db As Database
Dim rs As Recordset
Dim rsAss As Recordset
Dim InterimDate As Date

Dim startdate As Date
Dim enddate As Date
Dim giorni

Dim sql As String
Dim strCompo As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * from tblWorkOrder")

Set rsAss = db.OpenRecordset("tblMaintenance")
startdate = rs!StartDate
enddate = rs!endDate
days= rs!day

InterimDate = DateAdd("d", days, startdate)

Do Until InterimDate = enddate

rsAss.AddNew
rsAss!IdCompo = rs!IdCompo
rsAss!IdLocale = rs!IdLocale
rsAss!IdEdificio = rs!IdEdificio
rsAss!IdRisorsa = rs!IdRisorsa
rsAss!IdNomeIntervento = rs!IdNomeIntervento
rsAss!DataAssegnazione = rs!DataAssegnazione
rsAss!DataEsecuzione = InterimDate
rsAss!DataChiusura = rs!DataChiusura
rsAss!Eseguito = rs!Eseguito
rsAss!DurataPrevista = rs!DurataPrevista
rsAss!TempoImpiegato = rs!TempoImpiegato
rsAss!Stato = rs!Stato
rsAss!Note = rs!Note
rsAss!Dettagli = rs!Dettagli
rsAss!Richiestoda = rs!Richiestoda
rsAss!Priorità = rs!Priorità
rsAss!Tipo = rs!Tipo
rsAss!IdFornitore = rs!IdFornitore
rsAss!CostiEsterni = rs!CostiEsterni
rsAss.Update

InterimDate = DateAdd("d", days, InterimDate)
Debug.Print InterimDate

Loop
This code work fine when the DateAdd function is like this:
InterimDate = DateAdd("d", 7, Startdate)

When I replace ' 7 ' with the variable 'days' the code loop beyond the
endate.

Where is the error?

Thank you for your help!!!!
Tiziana Venturini

[Wayne Morgan]

1) I don't see a Dim statement for "days" which makes it a variant data type
(it also means you haven't set Option Explicit, which I would highly
recommend). A variant data type will accept a Null value. Is there a value
being given to "days"?

2) The "end date" may not be a multiple addition of the "days" value. For
example, if I'm starting on 1/1/2004, ending on 1/17/2004 and adding 7 days
at a time, the addition will give me 1/8/2004, 1/15/2004, 1/22/2004,
1/29/2004, etc. As you can see, it NEVER equals 1/17/2004 so the loop will
never stop. Try >= instead of = to remove this problem.

--
Wayne Morgan
Microsoft Access MVP
"TIZIANA VENTURINI" <ti***************@its-spa.it> wrote in message
news:ff**************************@posting.google.c om...

I must generate a maintenance plan.
The system has many components who have at intervals various need of
maintenance.
I have a table that contains the start date , the end date and the
interval between every maintenace like this:

idcompo startdate enddate day
CA1 03/08/2004 06/15/2004 7
BR2 03/08/2004 06/15/2004 15

etc...

I write this code:

Dim db As Database
Dim rs As Recordset
Dim rsAss As Recordset
Dim InterimDate As Date

Dim startdate As Date
Dim enddate As Date
Dim giorni

Dim sql As String
Dim strCompo As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * from tblWorkOrder")

Set rsAss = db.OpenRecordset("tblMaintenance")
startdate = rs!StartDate
enddate = rs!endDate
days= rs!day

InterimDate = DateAdd("d", days, startdate)

Do Until InterimDate = enddate

rsAss.AddNew
rsAss!IdCompo = rs!IdCompo
rsAss!IdLocale = rs!IdLocale
rsAss!IdEdificio = rs!IdEdificio
rsAss!IdRisorsa = rs!IdRisorsa
rsAss!IdNomeIntervento = rs!IdNomeIntervento
rsAss!DataAssegnazione = rs!DataAssegnazione
rsAss!DataEsecuzione = InterimDate
rsAss!DataChiusura = rs!DataChiusura
rsAss!Eseguito = rs!Eseguito
rsAss!DurataPrevista = rs!DurataPrevista
rsAss!TempoImpiegato = rs!TempoImpiegato
rsAss!Stato = rs!Stato
rsAss!Note = rs!Note
rsAss!Dettagli = rs!Dettagli
rsAss!Richiestoda = rs!Richiestoda
rsAss!Priorità = rs!Priorità
rsAss!Tipo = rs!Tipo
rsAss!IdFornitore = rs!IdFornitore
rsAss!CostiEsterni = rs!CostiEsterni
rsAss.Update

InterimDate = DateAdd("d", days, InterimDate)
Debug.Print InterimDate

Loop
This code work fine when the DateAdd function is like this:
InterimDate = DateAdd("d", 7, Startdate)

When I replace ' 7 ' with the variable 'days' the code loop beyond the
endate.

Where is the error?

Thank you for your help!!!!
Tiziana Venturini