displaying deadline each 10 days

[ana87]

Hello,
I writing a code that manages the deadlines of the files until now it was pretty simple they have only asked me to display the deadline of (n_file) one time after 10 days ( adddate to (reception_date)) and if = to date() the insertion is automatic in another table that contain ( N_file, deadline).

BUT yesterday I was asked:
while the (final_date) is empty the deadline date should be displayed every 10 days, and on top of that STAYING displayed for 5 days, HOW can i manage that seriously.

my code:

Dim rsreg As ADODB.Recordset
Dim rsintregreg As ADODB.Recordset


Set con = CurrentProject.Connection
Set rsreg = New ADODB.Recordset

''
rsreg.Open "[TabInterval]", con, 1, 3

'
Do While rsreg.EOF = False

N_files= rsreg("n_files")
date_recp = rsreg("Date_Reception")
date_fina = rsreg("date_final")

If IsNull(date_fina) Then
        date_recp = DateAdd("d", 10, [date_recp])
End If
   
   

       
rsreg("rap_reception") = date_recp
rsreg.MoveNext
Loop
'
Set rsreg = Nothing

GoTo Echenq

'req deadline date reg
Echenq:
Dim date_sys As Date

date_sys = Format(Now, "dd/mm/yyyy")
Dim ech As Date

Dim rsg As ADODB.Recordset
Dim rsp As ADODB.Recordset


'
Set rsg = New ADODB.Recordset
Set rsp = New ADODB.Recordset


rsg.Open "[TabIntervall]", con, 1, 3
rsp.Open "[not_Recep]", con, 1, 3
'DoCmd.RunSQL "drop table dbo.not_Recep"

sqlIntEch2 = "delete from dbo.not_Recep "
DoCmd.RunSQL sqlIntEch2

sqlIntEch3 = "delete from dbo.not_rep"
DoCmd.RunSQL sqlIntEch3


Do While rsg.EOF = False

date_rec = rsg("rap_reception")
N_files = rsg("n_files")

ech = date_rec



        If ech = date_sys Then
        rsp.AddNew
        rsp("n_files") = n_doss
        'rsp("Date_Ech") = ech
        rsp.Update
        rsp.MoveNext
        End If
         
      
                  
'Debug.Print ech, "___", n_doss
rsg.MoveNext
Loop


con.Close
Set con = Nothing
Set rsg = Nothing
Set rsp = Nothing

any idea?

 

[Ranman256]

This could have been done with a single query. No coded needed.

 

[CJ_London]

not clear from your description how you are using this data

but you know the start date, the interval, todays date and whether or not there is an end date

so the basic calc would be

if endate is null or startdate+interval<=enddate then
if startdate+interval between today and today+5 then show

however after you have passed the first interval the code won't work because startdate+interval will never be between today and today+5 again.

So you need to modify the interval

so what do you need to know - the startdate, the interval and todays date.

you can determine the number of days between startdate and today
you can then determine the number of times the complete interval has been 'used' during this period (e.g 100\13=7) as a multiplier for the interval

And as Ranman says, this can all be accomplished in a query

 

[Grumm]

GoTo Echenq

Now that is something I haven't seen since 1999
Maybe now is the time to not only add that new feature but also revamping the code in a more efficient way.

 

[ana87]

i really thank you for your help, and to be more clear;

the only date that i receive is the (date_reception) and if the (date_final) is not yet received ( because usually they send i paper with the this detail,
(file number, detail file, ..., final date).

if the final date is not received then a recall must be send mentioning (N_file) where the (final date) is messing.

that's why a query should be done to follow the hole recall process.

so the (date_final) is null until the reception of the paper,
the (n_file) should be displayed after each 10 days. to send a recall to send us the information.


so how to calcul the interval??
there is no date end, because the date end is the day when the (file is received with date_final).

and CJ LONDON, i'm sorry i really don't get you idea:

if endate is null or startdate+interval<=enddate then
if startdate+interval between today and today+5 then show

and if this could be accomplished in a query how to do so?

 

[sneuberg]

Now that is something I haven't seen since 1999
Maybe now is the time to not only add that new feature but also revamping the code in a more efficient way.

That's kind of weird too as the label is just after the Goto

GoTo Echenq

'req deadline date reg
Echenq:

I wonder why that was in there.

 

[CJ_London]

and CJ LONDON, i'm sorry i really don't get you idea:

I did say I didn't understand your description, so may have got it wrong but substitute your date_reception for startdate, date_final for enddate, interval is hardcoded as 10

and if this could be accomplished in a query how to do so?

using iif and/or switch functions

 

[ana87]

That's kind of weird too as the label is just after the Goto

GoTo Echenq

'req deadline date reg
Echenq:

I wonder why that was in there.

i actually changed the code there were another part just before the goto,
"On Error goto GoTo Echenq"
i forgot to remove it.

and really is it that old? we use to use it a lot at prog class!!! maybe the teacher was really old minded

 

[ana87]

I did say I didn't understand your description, so may have got it wrong but substitute your date_reception for startdate, date_final for enddate, interval is hardcoded as 10

using iif and/or switch functions

thx a lot, and sorry i'm really bad at explaining my bad.
i'll try what you have suggest.

 

[Grumm]

i actually changed the code there were another part just before the goto,
"On Error goto GoTo Echenq"
i forgot to remove it.

and really is it that old? we use to use it a lot at prog class!!! maybe the teacher was really old minded

Well for me personally during prog class they strongly avoid the use of GoTo.
Same as any return line in the middle of the function.

The last time I used GoTo was in Assembly code. But now in almost every object oriented programming language you just make functions that returns a value and continue with the code.

But this is all personal preferences

 

[ana87]

guys i'm really stuck:

the date_end is empty it's not logic to use in a condition as CJ_London said:

if endate is null or startdate+interval<=enddate then

when i execute it shows me the hole records.

 

[Grumm]

Maybe because of the typo in the first "end(d)ate"

 

[CJ_London]

Since I still do not understand what you are trying to achieve, and you have simply posted my pseudo code back rather than the code you are actually using, I cannot see how I can offer a suggestion.

 

[ana87]

i explain:
i have a table that i fill with : ( n_file, and reception date, detail file, and final date ),
the only date that i receive is the (date_reception) and if the (date_final) is not yet received ( because usually they send i paper with the this detail,
(file number, detail file, ..., final date).

if the final date is not received then a recall must be send mentioning (N_file) where the (final date) is messing.

that's why a query should be done to follow the hole recall process.

so the (date_final) is null until the reception of the paper,
the (n_file) should be displayed after each 10 days. to send a recall to send us the information.
and should stay displayed for 5 days.


hope i'm more clear?

 

[ana87]

i found a solution yeyyyy.
finally... even for displaying data for 5 days.