Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    67

    Unanswered: Sorting Dates in a Report

    I have a report showing a three-year audit schedule. Most business areas will be audited only once in the three-year period, but a few will be audited both in 2004 and 2006.

    I'm trying to get a completely chronological schedule of audits. The attached one-page Word doc shows the query and sort/group method. This set-up works fine except that it won't sort all of 2004 in order; instead, it sorts the 2004 w/2006 in order, then starts 2004 over again in January, then 2005, then the remainder of 2006.

    Is there a way to set it up so that when sorting 2004, it ignores the associated 2006 dates (which do not have to be in order, obviously)? See Current Results and Desired Results on attachment.

    Thanks much for any solutions!
    Attached Files Attached Files
    Last edited by Jaycee; 09-29-03 at 14:36.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Have you tried rearranging your sort orders? do your Blank year paired with nonempty year? And order it ascending (2004, 2005, 2006) ...

  3. #3
    Join Date
    Sep 2003
    Posts
    67

    Sorting Dates in a Report

    Thanks for your quick response! I tried out your suggestion, as well as several other sorting scenarios, but none gave the desired result. I'm open for any other thoughts you might have.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    Here's an evil thought: Have you considered a hidden column? Adding a ordering column for your dates that is set by your query? I do believe an autonumber would do the trick ... The only thing to be aware of is that order of entry determines order of display ...

    - Mike

  5. #5
    Join Date
    Sep 2003
    Posts
    67

    Sorting Dates in a Report

    This sounds promising. The query sorts the records accurately, so you're saying that if I add a column to the query that attaches ascending numbers to the records, then sort the report only on that (hidden) column, the dates will fall into the right order? If I'm following you correctly, could you tell me what that column should look like in the query?

    If I'm off-track re your idea, set me straight! Thanks!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    Ah ... You're basing your report on the query itself? The scheme I'm talking about would require using either an intermediate table or some coding to pull the data and put it into a table in the order necessary for the report to generate from ... Sorry about that ...

    On the other hand ...

    Now, if you had a "template" of your query as a table WITH an autonumber column in it so that you could change your query into an INSERT query then run the report ... That might work ...

    - Mike

  7. #7
    Join Date
    Sep 2003
    Posts
    67

    Sorting Dates in a Report

    The template idea sounds workable, but it's not something I already know how to do and would need step-by-step instructions ...

    I know this sounds archaic, but what if I created a field for the order number and entered it manually for each record? There are only 72 records in the db, so this wouldn't be horribly time consuming, and seeing as I've spent 6 weeks mining for a solution, it's pretty quick by comparison. Whatcha think?

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Jay,

    Your idea I don't think will work for you ... Remember: the report runs off of a table or query ... So you can do 1 of 2 things: either write code in the report to retrieve the data (like me) or develop a query to do the same ... Having a "template" is an easy thing ... I just call it a template. All it is is a table used to dump the data in ... Creating it is just like making any other table: Design View. Dumping data into it is the same as any other insert query: construct it in Query Design. Catching on?

    - Mike

    You can manually edit the table before running if you wish but, there goes flexibility and selectability out the window ...

  9. #9
    Join Date
    Sep 2003
    Posts
    67

    Sorting Dates in a Report

    Not being a programmer, me writing code is right out.

    I'm willing to try the template, and I get the basis of the idea, but I'm lost on the details. I know how to create basic tables and queries, but I'm unfamiliar with the terms "dumping data" and "insert query."

    I need a table with an autonumber field, and I need to dump the data into the table in chron order. How do I accomplish that, exactly? What's an insert query and how is data dumped into it? How would this query look different from the one that sorts the dates correctly (see doc attached to original post)?

    Please connect the dots for me. Thanks much for your patience and assistance!!

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You do know how to create a table in Access don't you? An insert query is a different TYPE of query ... Technically it's an Append Query. You set this by right clicking in the QBE window and selecting the "Query Type" menu topic. Don't wory about data dumping ... This is done automatically by the query (depending on the type of query it is) ...

    An example of some of my code: (Showing off ... )

    Code:
    Option Compare Database
    Option Explicit
    
    Dim SQLString As String
    
    Dim RptTbl As String
    Dim WONumber As String
    
    Dim DBConnectionActive As Boolean
    
    Dim MyConnect As ADODB.Connection
    Dim MyRecSet As ADODB.Recordset
    
    '
    ' *** Beginning of Functions and Methods
    '
    
    Function RetrieveChgDetail() As Integer
        On Error GoTo Err_RCD
        
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorLocation = adUseClient
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        
        ' Delete any existing records ...
        SQLString = "DELETE FROM " & RptTbl & ";"
        FormsConnect.Execute SQLString, , adCmdText
        
        ' Set the W.O. # and Project Title
        WO_Lbl.Caption = WONumber
        SQLString = "SELECT [Project Title] FROM [Work Orders II] WHERE (WorkOrderNumber='" & WONumber & "');"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            ProjectLbl.Caption = MyRecSet.Fields(0).Value & ""
        End If
        MyRecSet.Close
        
        ' Retrieve timesheet detail for the selected W.O. #
        SQLString = "SELECT [Clock Card Archive].DepartmentID, [CS Departments].Name, [Clock Card Archive].[Reg Hours], [Clock Card Archive].[OT Hours], [Clock Card Archive].[DT Hours],  [Clock Card Archive].[Payroll Date], Left([CS Employees].[First Name],1) & '. ' & [CS Employees].[Last Name] AS Expr1"
        SQLString = SQLString & " FROM ([Clock Card Archive] INNER JOIN [CS Departments] ON [Clock Card Archive].DepartmentID = [CS Departments].DepartmentID) INNER JOIN [CS Employees] ON ([Clock Card Archive].[Clock Card #] = [CS Employees].[Clock Card #]) AND ([CS Departments].DepartmentID = [CS Employees].DepartmentID)"
        SQLString = SQLString & " WHERE ([Clock Card Archive].WorkOrderNumber='" & WONumber & "');"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Department ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Department Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                ' Regular Hours
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(2).Value
                ' OT Hours
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(3).Value
                ' DT Hours
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(4).Value
                ' Payroll Date
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(5).Value
                ' Employee Name
                TrgRecSet.Fields(6).Value = MyRecSet.Fields(6).Value & ""
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        RetrieveChgDetail = 1
    Exit_RCD:
        Exit Function
        
    Err_RCD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveChgDetail = 0
        Resume Exit_RCD
    End Function
    
    Function EstablishDBConnection() As Integer
        On Error GoTo Err_EDBC
        
        Set MyConnect = New ADODB.Connection
        Set MyRecSet = New ADODB.Recordset
        
        MyConnect.CursorLocation = adUseClient
        MyConnect.Open "DSN=Billing System;" 'Pwd=XXXXXXXXXXXX
        
        MyRecSet.CursorLocation = adUseClient
        MyRecSet.CursorType = adOpenForwardOnly
        MyRecSet.LockType = adLockOptimistic
        
        EstablishDBConnection = 1
    Exit_EDBC:
        Exit Function
        
    Err_EDBC:
        
        MsgBox Err.Number & ": " & Err.Description
        EstablishDBConnection = 0
        Resume Exit_EDBC
    End Function
    
    Private Sub Report_Open(Cancel As Integer)
        Dim IsOK As Integer, ParmListString() As String
    
        IsOK = EstablishDBConnection
        If IsOK = 0 Then
            Cancel = 1
            Exit Sub
        End If
        DBConnectionActive = True
        
        ' Format:
        '
        ' Labor Charges Detail Report table, W.O. #
        '
        ParmListString = Split(Forms![Work Order Entry].ParameterTxt, ",", -1, vbTextCompare)
    '    ParmListString = Split(Me.OpenArgs, ",", -1, vbTextCompare)
        RptTbl = ParmListString(0) & ""
        WONumber = ParmListString(1) & ""
        
        RetrieveChgDetail
        
        Me.RecordSource = RptTbl
    End Sub
    
    Private Sub Report_Close()
        If DBConnectionActive Then
            MyConnect.Close
            Set MyRecSet = Nothing
            Set MyConnect = Nothing
        End If
    End Sub

  11. #11
    Join Date
    Sep 2003
    Posts
    67

    Sorting Dates in a Report

    Thanks, Mike -- I have it working now!

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And how did you do it? I'm curious ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •