Determining Avg Days Late on Mlg 1

[kentwoodjean]

I have a report made from a query to determine the average days late on specific mailings. We have a 2 business day turn-around. I am having difficulty trying to get the calculations correct. I was pretty excited at what I had done, but I am sure it is not correct. Following are the entries on my query:

Request Date

Date Sent

Item Number

Days Elapsed: [Date Sent]-[Request Date]

Days Late: [Days Elapsed]-2
(note- I do not have the calulation for avg days late but do need to have this)
The reason that I did the minus 2 was that assuming 4 days had elapsed, it would be 2 days late. That, however, does not account for the 2 Business days since if the request date was Friday, they would have until Monday to mail. I believe the way I have it is a flat out 2 days. The end result is if no days elapsse, the days late shows -2, if 1 day mailing -1, if 2 days, 0, if 3 days +1 etc.

 

[PaulBricker]

I built this function assuming that the 2 day turn around included the Request Date and the Date Sent. You can put this function in a module

Function dLate(rDate As Date, sDate As Date) As Integer
Dim SendBy As Date
Dim i As Integer
  If Weekday(rDate) = 6 Then
    SendBy = rDate + 3
  Else
    SendBy = rDate + 1
  End If
   If SendBy = sDate Then
     dLate = 0
   Else
     Do Until SendBy = sDate
      If Weekday(SendBy) = 7 Or Weekday(SendBy) = 1 Then
       i = i
       SendBy = SendBy + 1
      Else
       i = i + 1
       SendBy = SendBy + 1
      End If
     Loop
     dLate = i
   End If
End Function

Then call it from a query using this

Days Late:dLate([Request Date],[Date Sent])

This should get you close to what you need.

Paul

 

[PaulBricker]

I have a typo in there. I say
If SendBy = sDate Then
dLate = 0

it should be

If SendBy <= sDate Then
dLate = 0

Paul

 

[kentwoodjean]

Well, this certainly has been a learning experience. I put the function in by following the instructions in the Access help. Here is what it looks like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Function dLate(rDate As Date, sDate As Date) As Integer
Dim SendBy As Date
Dim i As Integer
If WeekDay(rDate) = 6 Then
SendBy = rDate + 3
Else
SendBy = rDate + 1
End If
If SendBy <= sDate Then
dLate = 0
Else
Do Until SendBy = sDate
If WeekDay(SendBy) = 7 Or WeekDay(SendBy) = 1 Then
i = i
SendBy = SendBy + 1
Else
i = i + 1
SendBy = SendBy + 1
End If
Loop
dLate = i
End If
End Function



At one point I got an error stating that I needed to end with &quot;End Sub&quot; since it started that way, so I tried it and it didn't work. Also pulled the first statement out since I thought it may not be necessary and just ended with End function. No luck. When I put the statement on the query,
Days Late:dLate([Request Date],[Date Sent])
I get the error: &quot;Undefined function dLate in Expression&quot;
Since this is a bit new to me, a little more help would be appreciated. Thanks.

 

[PaulBricker]

In the Database window you will see tabs for Tables, Queries, Forms...and Modules. Click on the modules tab. Select New. Copy and paste the entire Function I wrote including the End Function line and paste it into the Module. Now save the module as funDaysLate (you can name the module anything you want as long as it's not dLate). Then in your query in a new column put

Days Late:dLate([Request Date],[Date Sent])

Make sure your field names are Request Date and Date Sent. I just used those names because that's how you described it in your post. If those are the names, then the function should run just fine. The only catch will be if you have Null values in either of the Date fields. Post back if you have trouble.

Paul

 

[kentwoodjean]

I think that we are almost there. When I ran the query, I received a &quot;Debug Error&quot; which appears to be in last part of the function. During the Debug, the yellow highlight is with the first line...........i = i + 1. I'm not sure what it should be corrected to.

i = i + 1
SendBy = SendBy + 1
End If
Loop
dLate = i
End If
End Function

 

[PaulBricker]

Make sure i is Dimmed as an Integer. What are you getting for an error message exactly. That will help a lot. Also, post the entire function and the line in your query so I can double check everything.

Paul

 

[kentwoodjean]

The error I get is a RunTime error '6', Overflow.

Here is the complete function:

Option Compare Database
Option Explicit

Function dLate(rDate As Date, sDate As Date) As Integer
Dim SendBy As Date
Dim i As Integer
If WeekDay(rDate) = 6 Then
SendBy = rDate + 3
Else
SendBy = rDate + 1
End If
If SendBy <= sDate Then
dLate = 0
Else
Do Until SendBy = sDate
If WeekDay(SendBy) = 7 Or WeekDay(SendBy) = 1 Then
i = i
SendBy = SendBy + 1
Else
i = i + 1
SendBy = SendBy + 1
End If
Loop
dLate = i
End If
End Function

 

[PaulBricker]

Well I changed out one line. The line
If SendBy <= sDate
I changed to
If SendBy >= sDate
But that shouldn't make a difference in your Function other than to run correctly. <= always resulted in a 0 which wasn't correct because SendBy was always less that sDate. But that shouldn't have to do with Overflow. Do you have any date fields that don't have dates in them. That will make a difference because we are not set up to handle Null values.

Function dLate(rDate As Date, sDate As Date) As Integer
Dim SendBy As Date
Dim i As Integer
If Weekday(rDate) = 6 Then
SendBy = rDate + 3
Else
SendBy = rDate + 1
End If
If SendBy >= sDate Then
dLate = 0
Else
Do Until SendBy = sDate
If Weekday(SendBy) = 7 Or Weekday(SendBy) = 1 Then
i = i
SendBy = SendBy + 1
Else
i = i + 1
SendBy = SendBy + 1
End If
Loop
dLate = i
End If
End Function

Paul

 

[kentwoodjean]

Paul,

Well, we got it.....I think. Below is what the output looks like. My only question is why in some cases 3 days go by and it is 2 days late and in other cases 3 days go by and it is 0 days late. The same is true for other times as well. Thought I would have you take a look at it for your opinion. I am not receiving any errors but I just need to understand why the output varies. Thanks for all your help.

Request Sent Item Days Days
Date Date Elapsed Late

10/28/02 11/1/02 NB0019 4 3
10/28/02 11/1/02 cb6468 4 3
10/29/02 11/4/02 4593 6 3
10/29/02 11/4/02 4593 6 3
10/29/02 11/1/02 4559 3 2
10/29/02 11/1/02 CF4570 3 2
10/29/02 11/1/02 4593 3 2
10/30/02 11/4/02 cb6468 5 2
10/30/02 11/4/02 4593 5 2
10/30/02 11/4/02 4593 5 2
10/30/02 11/1/02 4593 2 1
10/31/02 11/4/02 4593 4 1
10/31/02 11/4/02 CF0002 4 1
10/31/02 11/4/02 cb6468 4 1
10/31/02 11/4/02 4559 4 1
10/31/02 11/4/02 4593 4 1
10/31/02 11/4/02 4593 4 1
10/31/02 11/1/02 CF4570 1 0
10/31/02 11/1/02 CF4147 1 0
10/31/02 11/1/02 4593 1 0
10/31/02 11/1/02 CF0002 1 0
10/31/02 11/1/02 4559 1 0
10/31/02 11/1/02 4593 1 0
10/31/02 11/1/02 4593 1 0
10/31/02 11/1/02 cb6468 1 0
10/31/02 11/1/02 4593 1 0
11/1/02 11/4/02 MedNG 3 0
11/1/02 11/4/02 NB0019 3 0
11/1/02 11/1/02 cs4589 0 0
11/1/02 11/4/02 MedNG 3 0
11/1/02 11/4/02 4593 3 0
11/1/02 11/4/02 4593 3 0
11/1/02 11/1/02 4593 0 0
11/1/02 11/1/02 4593 0 0

 

[PaulBricker]

OK, going from top to bottom for most of the records,
10/28 is a Monday, 11/1 is Friday. Monday and Tuesday is 2 days turn around and Wed, Thurs and Fri are 3 days late.
10/29 is Tues to 11/4 is Monday. Tues and Wed. are 2 turnaround days, Thurs Fri and Mon are 3 days late.
10/29 is Tues to 11/1 is Fri. Tues and Wed are 2 days turn around and Thru and Fri are 2 days late.
10/30 is Wed to 11/4 is Mon. Wed and Thurs is 2 day turn around and Fri and Mon are 2 days late
11/1/02 is a Friday, 11/4/02 is a Monday so although 3 days have passed, Friday to Monday in within the 2 day turn around.

If those numbers are not correct, let me know what they should be. As I said originally, I assumed that the 2 day turn around was the Request Date + the next day. Anything after that was late.

Paul

 

[kentwoodjean]

It all makes perfect sense now. AS a matter a fact, after I sent my last response to you, I studied the function and pretty much figured it out. Having it in english makes it much clearer though, and easier for me to explain to others.

You have been a great deal of help to me and I appreciate all the good information and learnings you have given me. I am on a 2-week vacation right now, and taking this time to clean up some of the loose ends on several of our data base programs.

 

[MichaelRed]

Hmmmmmmmmmmmmm,

Now that some mechanics are understood, you need to back up and start over. Ye olde story. Been there. Done that. Still missing the holidays, and you WILL hear about it soon. See faq181-261 for the calculation of BUSINESS days. It does require the implementation (and maintenance) of a table for the observed holiday dates, but the reward it no complaints re the incorrect calculation(s).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places