Date Issues

[simflex]

Hello everyone, (again)
I hope this question is not too cheap.
I am trying to use the dateAdd() to make some changes to a each value of a date.
This is part of my ongoing project.
For instance, if I have a date '02/20/2003', I will like to say if the db field is A then add one day to the above date.
If db field is B then add 2 days and if db field is C then add 3 days so my result will look like:
'02/21/2003', '02/22/2003', '02/23/2003'
My current code is not doing that because the date format is mm/dd/yyyy.
Is there any quick to do the date conversion so this code will work.
Here is the actual code I am using:

ScheduledDate = rst("DateofAccident")
Dim APPSchedule, ADDSchedule,DDASchedule
APPSchedule = DateAdd("d",1,ScheduledDate)
ADDSchedule = DateAdd("d",2,ScheduledDate)
DDASchedule = DateAdd("d",3,ScheduledDate)

<%
If rst("type") = "A" Then
Response.Write "<font color=""#000000""> " & APPSchedule & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font>"
ElseIf rst("type") = "B" Then
Response.Write "<font color=""#000000"">" & ADDSchedule & "</font>"
Else
Response.Write "<font color=""#000000"">" & DDASchedule & "</font>"
End If
%>

[simflex]

Hello everyone,
I have got some date issues I have been trying to resolve now for awhile with no success.
I have 3 different date values as you can see from assignment below.
SupervisorSchedule, ADDSchedule and DDASchedule

If for instance supervisorSchedule has a schedule of 07/23/2003, and today's date is 08/07/2003
and actualDate is null, this means that
that supervisor has missed deadline.
I will like to print a message that says 'Overdue'

If supervisorSchedule is schedule of 07/23/2003, and today's date is 08/07/2003
and actualDate has a date value, this means that even though supervisor has
missed the deadline, his/her work is completed so I want to print out a message that says:
'Completed'
If supervisorSchedule is 08/10/2003 and actualDate is empty or null, that
means that supervisor has not missed deadline yet.
So I will like to print a message that says'Pending'.
The same logic applies to ADD (ADDSchedule) and DDA (DDASchedule).

The code I have below is not helping me accomplish this.
Some of the records will say 'Completed' and the rest, if even they have been
completed, will print 'Pending'
Can I please ask someone to see what I am doing wrong?
Thanks everyone.


ScheduledDate = DateValue(rst("DateofAccident").Value)
SupervisorSchedule = DateAdd("d",1,ScheduledDate)
ADDSchedule = DateAdd("d",2,ScheduledDate)
DDASchedule = DateAdd("d",3,ScheduledDate)
currDate = date()
actualDate = rst("actual")

If ((SupervisorSchedule > currDate and ADDSchedule > currDate and DDASchedule > currDate) and actualDate ="") Then
Response.Write "<font color=""#FF0000"">" & rst("steps") & " is overdue</font>"
ElseIf ((SupervisorSchedule <> currDate and ADDSchedule <> currDate and DDASchedule <> currDate) and actualDate <> "") Then
Response.Write "<font color=""#009900"">" & rst("steps") & " is complete</font>"
Else
Response.Write "<font color=""steelBLUE"">" & rst("steps") & " is Pending</font>"
End If

[simflex]

hi Dave,
field type for DateofAccident and actual is datetime.

[simflex]

thank you very much Dave,
I tried using isNull before your post.
Obviously I was using incorrectly.
After making adjustments based on the test I ran with your code,
The code is almost working because when an order is completed, it prints "Completed"; when an order has not been completed and is past due, it is printing "Overdue" and when an order has not been processed and it is not past current date, it is printing "Pending". So almost there but there is still something wrong with the ELSE portion of the code because any process completed before currDate is printing "Pending"

Can you please see if you can point out where I am wrong?

currDate = date()
actualDate = rst("actual")
If (IsNull(actualDate) AND SupervisorSchedule < currDate and ADDSchedule < currDate and DDASchedule < currDate) Then
Response.Write "<font color=""#FF0000"">" & rst("steps") & " is overdue</font>"
ElseIf (NOT IsNull(actualDate) AND SupervisorSchedule <> currDate and ADDSchedule <> currDate and DDASchedule <> currDate) Then
Response.Write "<font color=""#009900"">" & rst("steps") & " is complete</font>"
Else
Response.Write "<font color=""steelBLUE"">" & rst("steps") & " is Pending</font>"
End If

Thanks again for your time and help.

[simflex]

Dave,
I believe I fixed it.
I added an extra IF statement that says:
ElseIf (NOT IsNull(actualDate) AND SupervisorSchedule >= currDate and ADDSchedule >= currDate and DDASchedule >= currDate) Then ....
Now everything seems to be working.
It is now to know experts and talents like you and Ribeyed exists in this world.
I am not putting you two in the same category but you possess different but extraordinary levels of skills.
Thank you!

[simflex]

Please allow me to inconvenience you again.
I have been playing around with this now for more than 3 hours and it is still not working.

My display screen is set up like this:

Play Date endDate Diff
A 07/23/2003
B 07/24/2003
C 07/25/2003

A 07/26/2003
B 07/27/2003
C 07/28/2003

Right now, 3 things are happening.
First, I am having same date difference appear in one group of rows.
For instance, row one, if the endDate is 08/08/2003 and startDate is 07/23/2003 in row A(SupervisorSchedule), I expect to see, 16.
if startDate in rowB (ADDSchedule) is 07/24/2003 and endDate is 08/08/2003, I expect to see 15 in row B, and if startDate in row C (DDASchedule) is 07/25/2003 and endDate is 08/08/2003, I expect to see 14 row C. Same with the second group of A, B, C

So far, I am getting the same number 13.
Second of all, that number is incorrect. It should read 16 for A, 15, for B and 14 for C.
Third, that number is inconsistent. In some cases where startDates and enddates are the same in some column, I get difference results.
Last but not least, if endDate is null or blank, it displays 0.
Obviously I am doing something terribly wrong.

The reason dates are handled in groups is that we are designing a safety program ( I am sure everyone is been following it here) whereby action is required from 3 different people.
When an event occurs, the first person in queue to take action is the supervisor.
The supervisor has 24 hours from the date the event occurs to do take some action.
Therefore if the date the event occurs is 08/08/2003, the supervisor will have till the end of 08/09/2003 (called supervisorSchedule) to take action.
The date the supervisor takes action is called the endDate.
So if the supervisor takes action on 08/10/2003), we know that the supervisor is 1 date late in taking action.
Same for the next person on line called Assistant Director.
He/she has 48 (called ADDSchedule) hours from the date of event and the DDA has 72 hours from date of event (DDASchedule) to take action.
All these assume that each will complete theirs on 24 hour span.
You have: StartDate EndDate Difference (in number of days)
SupervisorSchedule 08/01/2003
ADDSchedule 08/02/2003
DDASchedule 08/03/2003

From above schedule, the system will stick the end date in EndDate column once each individual takes action.
That is why we have them in groups so management can see that:
It took supervisor X number of days to take action, it took AD x number of days to take action and DA x number of dates take action between their schedule and end date.
This has been a pain and I am so pressed for time right now.
any assistance will be greatly appreciated.
Thanks for your response.

The below code is not working:

Do While Not rst.EOF
EndDate = rst("actualDate")
SupervisorSchedule = DateAdd("d",1,rst(dateofEvent))
ADDSchedule = DateAdd("d",2,rst(dateofEvent))
DDASchedule = DateAdd("d",3,rst(dateofEvent))

If SupervisorSchedule > endDate Then
days = DateDiff("d", SupervisorSchedule, enddate)
ElseIf ADDSchedule > endDate Then
days = DateDiff("d", ADDSchedule, enddate)
ElseIf DDASchedule > endDate Then
days = DateDiff("d", DDASchedule, enddate)
Else
days = 0
End If
Response.write " & days & past due date "
rst.MoveNext
Loop

[simflex]

hi Dave,
the only thing that is valid from the test you gave me is the IsNull test and that solved that problem that time.
The rst.Field("actualDate").Value is causing an error.

Object doesn't support this property or method: 'Field'
Of course when I removed 'Field', the error goes away, although the problem is still there.

[simflex]

No, unfortunately, it didn't fix anything.

Just to be sure I am using same logic, here it is the code again.

ScheduledDate = rst.Fields("DateOfAccident").Value
SupervisorSchedule = DateAdd("d",1,ScheduledDate)
ADDSchedule = DateAdd("d",2,ScheduledDate)
DDASchedule = DateAdd("d",3,ScheduledDate)
EndDate = rst.Fields("Actual").Value
If SupervisorSchedule > endDate Then
days = DateDiff("d", SupervisorSchedule, enddate)
ElseIf ADDSchedule > endDate Then
days = DateDiff("d", ADDSchedule, enddate)
ElseIf DDASchedule > endDate Then
days = DateDiff("d", DDASchedule, enddate)
Else
days = 0
End If
Response.Write "<font color=""#000000"">" & days & " days</font>"

[simflex]

ok, sure.
We are designing an accident tracking program whereby whenever an accident occurs, there are 3 steps that must be taken to ensure that process is completed successfully and on time.
So there are 3 major players here - Supervisor , Asst Director and Director.
So when an accident occurs, supervisor has 24 hours from date of accident (supervisorSchedule), Asst Director has 48 hours (ADDSchedule) and Director has 72 hours (DDASchedule) to complete a paperwork.

so let's say date of accident is 08/07/2003, that means that:

Supervisor has 08/8/2003, AD has 08/9/2003 and
DA has 08/10/2003.

What I have done is assign date of accident to schedule:
Schedule = ScheduledDate = rst.Fields("DateOfAccident").Value then
I assign schedule to supervisor (supervisorSchedule) with an increment of one day ,
assign schedule to ad (ADDSchedule) with an increment of 2 days, and an increment of 3 days to DA (DDASchedule), sll with the dateAdd().
Then we want to establish a completion date so that the date each of the players completes his/her paperwork, will be the endDate for that individual.
Finally, want to see if the individual completed his/her work within the date allocated or whether the individual or individuals missed deadline.
So the individual misses deadline, we want to display a message that says missed deadline by x number of days, for instance, 3 days past due date.

So essentially, we want to compare the date the paperwork was supposed to be completed (startDate) and the date it is completed and display the days past due.
If it is not past due yet, we just display "0 days past due"

Please let me know if this is not clear.
Thanks Dave for your assistance

[simflex]

that did work but the problem, Dave, is that is not possible to code it this way.
I won't know ahead of time what the schedule date is and certainly not when the actual date will occur.
But good job!

[simflex]

Easy Dave, easy. There is no reason to be angry here.
Let's factor in the possibility that one's comments may be mis-interpreted.
In this case, the following statement:

Then you will need an actual date for each schedule date.
was misunderstood by me and I apologize!

[shoefayre]

I am In AWE of you Dave...What Dave wrote is correct but lets look at it in a logic way (Back To Skool) simflex.

You are corrrect there are 3 steps...but we will do them a different way … lol

Step1

You will have a database, for sake of argument the fields could be:
Accidentn(*),accidentdate,supenddate,adirenddate,direnddate

(*)Accidentno will be unique to help identify the record later

In step one you will enter the date of the accident, and any other initial data.


Step2
This is a 3-part phase, at most, if all of the players enter values
At this phase you capture the various data and date (which you put into the above file, by locating the accident no first then, supenddate = mm/dd/yyyy (or in uk lol dd/mm/yyyy)
And so on)

We now have potentially all the data…


Step 3 could be a report or a screen to supply data or what ever…(This is the maths part)

Here like Dave said you check the accidentdate + allowed days (schedule date) against each individual completion date note if a player has not completed a record you should check for this…e.g. (this on one but you would do the same for each…)
(Note you will read these records from the database file and f you need a select record use the accident no to select a unique record)

If supenddate isNull then
Print they did not complete any work
Else (they did complete work so lets check)
If supenddate > schedule date (did they exceed the date allowed)
Work out date dif and print out (how many days over)
Else
Print the job was completed in time ok
End if
End if

Obviously with above statement you could store the data in a variable and read through records and display in a report or whatever you choose.

So step 1 = get accident date into DB
Step 2 = get, if any, the date jobs get done into DB
Step 3 = a bit of maths and logic to work out when it got done and is it ok and print a MSG accordingly etc…(this bit could be run at end time of course, because of the IF statement)

I would say that logic should get the job done, sorry not coded it but if you have the logic, coding is the easy part. I am sure Dave or somebody will correct me if I have a bit of logic wrong but this is a basic look at your job.

Best of luck Steve

[simflex]

sorry, I have not been back here since my last post.
Thanks Steve!