I am trying to evaluate aging tickets... breaking them up in age ranges. I've created a function that seems like it should do this, but when I run through the code the "TO" keyword in my Case statement doesn't seem to be working correctly.
here's my code"
Function Get_Time_Span(ByVal dtBeg As Date, ByVal dtEnd As Date, ByVal dtFinalDay As Date, ByVal intFillMonth As Integer) As String
Dim strSQL As String
Dim strRange, strLastDay As String
Dim dtFill As Date
Dim intAge, intMonthRange, intDaysAfterClose, intFillYear As Integer
Dim intFillPosition, intEndPosition, intBegPositon As Integer
'---------------------------------------------------------------------
' If Month(dtFinalDay) - intFillMonth is positive then Fill Month is
' in the same year as the Final Report Month.
' Negative means FIll Month is for the prior year
'---------------------------------------------------------------------
intFillPosition = Month(dtFinalDay) - intFillMonth
If intFillPosition > 0 Then
intFillYear = Year(dtFinalDay)
Else
intFillYear = Year(dtFinalDay) - 1
End If
'Put Fill date together
Select Case intFillMonth
Case 1, 3, 5, 7, 8, 10, 12
strLastDay = "31"
Case 4, 6, 9, 11
strLastDay = "30"
Case 2
If (intFillYear Mod 4) = 0 Then
strLastDay = "29"
Else
strLastDay = "28"
End If
End Select
'-----------------------------------------------------------------------
' Check if fill date falls within Create and resolve dates
' note that open tickets have a resolve date of dtFinalDay
'-----------------------------------------------------------------------
If dtBeg <= dtFill <= dtEnd Then
intAge = DateDiff("d", dtBeg, dtFill)
Select Case intAge
Case 1 To 9
strRange = "NA"
Case 10 To 29
strRange = "10 - 29"
Case 30 To 59
strRange = "30 - 59"
Case 60 To 89
strRange = "60 - 89"
Case 90 To 999
strRange = "90+"
Case Else
strRange = "NA"
End Select
Else
strRange = "NA"
End If
Get_Time_Span = strRange
End Function
Sub TestCase()
Dim intage As Integer
Dim strrange As String
intage = 460
Select Case intage
Case 1 To 9
strrange = "NA"
Case 10 To 29
strrange = "10 - 29"
Case 30 To 59
strrange = "30 - 59"
Case 60 To 89
strrange = "60 - 89"
Case 90 To 999
strrange = "90+"
Case Else
strrange = "NA"
End Select
Debug.Print intage & " " & strrange
End Sub
But it doesn't seem to make much difference on how it evaluates. In either event it actually does get evaluated to true (see link:
): when dtBeg is less than or equal to dtFill and dtFill is less than or equal to dtEnd... The code is getting past this point.
I am not syaing this part of the code isn't wrong.... it verywell may be... but the issue that I am asking about doesn't appear to be caused by this, this issue seems to happen after said evaluation.
So... my issue is this: for some reason my integer variable is not being evaluated correctly in the case statement.
When I use the watch window I see the variable posted as variant/long... The Capture.JPG link, I posted, clearly shows the 460 value skipping past the last case (i.e., 90 to 999) and getting to "case else".
Because I Dim'd the variable as an int, I even changed this line to convert the long/variant into an integer, just in case that was the issue:
Code:
intAge = CInt(DateDiff("d", dtBeg, dtFill))
But that did nothing either. For some reason my Case statement isn't understanding the value in the variable named intAge, which is created by the DateDiff function (shown above).
PS. If anyone would like to run the function I posted with the values I am running... I'll post them below.
Note: These values are being pushed programmatically... I captured them in the watch window and am posting them by hand... so if there's an error below, that'll just be a typo... and should not be a reason for the eval not working.
OK I am not trying to spam my own thread here... but I changed my case statement to this:
Code:
Select Case intAge
Case Is < 10
strRange = "NA"
Case Is < 30
strRange = "10 - 29"
Case Is < 60
strRange = "30 - 59"
Case Is < 90
strRange = "60 - 89"
Case Is < 9999
strRange = "90+"
Case Else
strRange = "NA"
End Select
And it works just fine... don't know what was up with the "To" keyword... but for whatever reason, it wasn't working for me... I'd still like to know the answer to that, if anyone knows why it wasn't working.... but for now, it looks like I can move on
Thanks for the comments and everyone's attention.... I very much appreciate all the help!!!
not sure what you are tring to do exactly - but you can subtract dates directly. VBA holds dates as a real number. THe integer is the nomber of whole days sinnce a gven base date. The decimal bit is the fraction of a day.
With the exact code as posted in post #1 and the argument data as posted in post #6.
With Australian and USA regional settings, which should not have been a problem anyhow because the days are greater than 12.
With today being in May there should be no boundary date problem.
With Access 2003 on an XP box.
Even with the Variants Brian mentioned.
It works; it returns 90+
Code:
Option Compare Database
Option Explicit
Sub TestIt()
Dim dtBeg As Date
Dim dtEnd As Date
Dim dtFinalDay As Date
Dim intFillMonth As Integer
dtBeg = #10/28/2009 10:17:45 AM#
dtEnd = #4/30/2011 11:59:59 PM#
dtFinalDay = #4/30/2011 11:59:59 PM#
intFillMonth = 1
MsgBox Get_Time_Span(dtBeg, dtEnd, dtFinalDay, intFillMonth) [color=green]' << 90+[/color]
End Sub
Function Get_Time_Span(ByVal dtBeg As Date, ByVal dtEnd As Date, ByVal dtFinalDay As Date, ByVal intFillMonth As Integer) As String
Dim strSQL As String
Dim strRange, strLastDay As String
Dim dtFill As Date
Dim intAge, intMonthRange, intDaysAfterClose, intFillYear As Integer
Dim intFillPosition, intEndPosition, intBegPositon As Integer
'---------------------------------------------------------------------
' If Month(dtFinalDay) - intFillMonth is positive then Fill Month is
' in the same year as the Final Report Month.
' Negative means FIll Month is for the prior year
'---------------------------------------------------------------------
intFillPosition = Month(dtFinalDay) - intFillMonth
If intFillPosition > 0 Then
intFillYear = Year(dtFinalDay)
Else
intFillYear = Year(dtFinalDay) - 1
End If
'Put Fill date together
Select Case intFillMonth
Case 1, 3, 5, 7, 8, 10, 12
strLastDay = "31"
Case 4, 6, 9, 11
strLastDay = "30"
Case 2
If (intFillYear Mod 4) = 0 Then
strLastDay = "29"
Else
strLastDay = "28"
End If
End Select
dtFill = CDate(CStr(intFillMonth) & "/" & strLastDay & "/" & CStr(intFillYear))
'-----------------------------------------------------------------------
' Check if fill date falls within Create and resolve dates
' note that open tickets have a resolve date of dtFinalDay
'-----------------------------------------------------------------------
If dtBeg <= dtFill <= dtEnd Then
intAge = DateDiff("d", dtBeg, dtFill)
Select Case intAge
Case 1 To 9
strRange = "NA"
Case 10 To 29
strRange = "10 - 29"
Case 30 To 59
strRange = "30 - 59"
Case 60 To 89
strRange = "60 - 89"
Case 90 To 999
strRange = "90+"
Case Else
strRange = "NA"
End Select
Else
strRange = "NA"
End If
Get_Time_Span = strRange
End Function
I don't know what's happening at your end.
Edit:
Try putting together a sample of how the function is called; a sample which causes the error.
The method of calling the function may be the problem.
Without being able to reproduce the error we really can’t say what the problem is.
I am learning a lot from this one post. I didn't realize that when you list variable with commas only the last one gets the designated data type... Hmm... good to know.
Also subtracting dates directly gives you the day.That makes things eaiser.
For everyone who asked what I am doing... it's kind of involved but I will elaborate just for clarification purposes:
I am a reports guy... and I was asked to show a rolling year's worth of ticket data (for a help desk). i.e., the aging cases by month for all tickets in four categories, arbitrarily set by management, (i.e., "10 - 29", "30 - 59", "60 - 89", and "90+").
To do this I have to find when the ticket was created (i.e., dtBeg), when it was resolved (i.e., dtEnd), and note when the report is being run (dtFinalDay).
To get a rolling year I have created a table which pulls all the tickets in and adds twelve month fields to it... Jan - dec. What I am trying to do is define the range for each month against any given ticket. The one thing that makes this a doozie is that we're talking about a rolling year, thusly any month might be from the current year, or it might be from last year, depending upon when the report is being pulled. So I have to reference the dtFinalDay against the intFillMonth to not which year the "fill month" (i.e., the month field I am trying to populate in my table) resides.
So if I run the report today, the last full month would be April, thusly the dtFinalDay will be 4/30/2011...
Lets say ticket 1 was opened in 2009 and never closed, Jan through Dec will be marked as "90+"...
If ticket 2 was opened 1/21/2011, and closed 4/1/2011
it would show:
Jan = "10 - 29", feb = "30 - 59", Mar = "60 - 89", Apr = "60 - 89" Then May - Dec = "NA", because the ticket wasn't open within those months.
So I call my function from a sub routine that is looping through a recordset from first to last record and also looping through each of the month fields from left to right. The last variable in my function denotes the field postion (i.e., 1 for Jan, 2 for Feb, etc...)
For Example:
Code:
'------------------------------------------------
' Open table for adding date ranges
'------------------------------------------------
strSQL = "SELECT * FROM CASES;"
rs.Open (strSQL)
rs.MoveFirst
Do While Not (rs.EOF And rs.BOF)
'------------------------------------------------
' There are 31 fields in the CASE table
' (i.e. 0 to 30).
' Dump strAgeBucket into corresponding
' appropriate fields.
' 19 through 30 denote the months Jan - Dec
'------------------------------------------------
For i = 19 To 30
intFill = i - 18
strAgeBucket = Get_Time_Span(rs!Create_Date_MDY, rs!Resolved_TIMESTAMP_MDY, rs!Report_Date, intFill)
rs.Fields(i) = strAgeBucket
rs.Update
Next i
rs.MoveNext
Loop
Once I have the fileds filled in the way I want, I will aggregate all these values (by range category) to show a count of aging tickets each month broken down by varrious things like each of the divisions... maybe by team etc...
Anyway... I know the logic isn't quite there just yet... it's a work in progresss. But people seemed to be confused as to why I would be doing what I am doing, so I figured I'd explain... Especially since everyone has been so helpful.
I really appreciate all the comments, I have been doing this stuff for a long time now, and you guys always tell me stuff that I feel I should have known years ago but never learned.
Sorry it took me so long to get back. I actually ran with the other way of running the case statement to finish my project.
This morning I went back and took the code you posted (that ran fine on your end). I ended up changing the name of the function to Get_TimeSpan2 (since I am using the other one and didn't want to mess with it.
For me, it still does NOT run correctly (intAge = 460, yet it is not being evaluated in the 90 to 999 case).
I am sending you a screenshot of the debug:
I don't know why... but others seem to have no issues with this, but mine is not evaluating correctly (as shown in the debug screen shot)
