Aging Report

[camidon]

I'm trying to create an aging report that will tell the age of opened trouble tickets in our support software. I am given an opendate and other details that need to be in the report. But what I would like to do is not only display opendate, but show how old the tickets are (ie., current, one week old, two weeks old, three weeks old, ect.) I would also like to sort this by oldest ticket first. Is there any way to create a report to do this or am I going to have to do an additional step beforhand like base it on a special query?

Please let me know if any more info is needed.

Chris

 

[Aivars]

Hi!

FileAgeInWeeks=datediff("w",FileLastUpdate,date)

If you use Application.FileSearch for analyse of files attributes you can use parameters of .Execute:

expression.Execute(SortBy, [SortOrder], [AlwaysAccurate])

SortBy Optional Variant. The method used to sort the returned files. Can be one of the following MsoSortBy constants: msoSortbyFileName, msoSortbyFileType, msoSortbyLastModified, or msoSortbySize.

SortOrder Optional Variant. The order in which the returned files are sorted. Can be either of the following MsoSortOrder constants: msoSortOrderAscending or msoSortOrderDescending

Example of list box rowsource creating (four columns):

me.lstBox.rowsource=ListOfFileLocation("*.mdb", "C:\MyDirectory\", False, 4, 2)

Function ListOfFileLocation(Optional strFileName As String = "*.*", _
Optional strFolder As String = "C:\", _
Optional blnSubFolders As Boolean = True, _
Optional intSortBy As Integer = 1, _
Optional intSortOrder As Integer = 1) As String

'strFileName --> File Name mask like "DB*.mdb"
'strFolder --> locations directory (e.g. "C:\Temp\&quot Default="C:\"
'blnSubFolders --> Search In Sub Folders yes or no (Default=True)

'varSortBy --> The method used to sort the returned files
'msoSortByFileName = 1 - Default
'msoSortBySize = 2
'msoSortByFileType = 3
'msoSortByLastModified = 4

'varSortOrder --> The order in which the returned files are sorted
'msoSortOrderAscending = 1 - Default
'msoSortOrderDescending = 2

Dim strFileList As String
If Right(strFolder, 1) <> &quot;\&quot; Then
strFolder = strFolder & &quot;\&quot;
End If
With Application.FileSearch
.LookIn = strFolder
.SearchSubFolders = blnSubFolders
.FileName = strFileName
If .Execute(intSortBy, intSortOrder) > 0 Then
For i = 1 To .FoundFiles.Count
'Compose list box row source clause
If strFileList <> &quot;&quot; Then
strFileList = strFileList & &quot;;&quot;
End If
'File Name; File Created; File's Last Accessed; Last Modified
strFileList = strFileList & FileArgs(.FoundFiles(i))
Next i
Else
MsgBox &quot;File ''&quot; & strFileName & &quot;'' not found&quot;
End If
End With

ListOfFileLocation = strFileList
End Function

List of File's properties (four):
Function FileArgs(FileSpec)
Dim fs, f, s
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
Set f = fs.Getfile(FileSpec)
'File Name
s = FileSpec & &quot;;&quot;
'File Created
s = s & f.DateCreated & &quot;;&quot;
'File's Last Accessed
s = s & f.DateLastAccessed & &quot;;&quot;
'Last Modified
s = s & f.DateLastModified

FileArgs = s
End Function

Aivars

 

[camidon]

I'm working with recordsets, not files. I don't know if this still pertains to what I'm trying to do or not, but could someone please shed some light on this for me?

 

[JoeMiller]

You should make a special query that has a field to figure out the date difference of todays date and your call date. Then that field can be sorted on in your report. The function to use is DateDiff so in your query make a field like so:
[tt]
WeeksAged: IIf(DateDiff(&quot;ww&quot;,[MyDateField],Date())<1,&quot;Current&quot;,DateDiff(&quot;ww&quot;,[MyDateField],Date())&&quot; Week(s)&quot
[/tt]
This equation will then show Current if the dates are 0 weeks apart and &quot;# Week(s)&quot; when they are less than 1 weeks old (including future dates if they exist).

HTH Joe Miller
joe.miller@flotech.net