Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Unanswered: Custom ordering on a report (SOS)

    Hey Guys, I have another annoying question for you gurus to help me solve

    I have a query taking out many dates from a table. Each date, has an ID, of course, and identity number, which can be from 1 to 3. Here are a few entires as example:

    01/01/2005 - 1
    23/12/2004 - 2
    11/05/2004 - 3
    24/04/2005 - 2

    And so on.

    My query works great, and takes only dates within one month, for example I can pull all november 04 dates, or 05.. all fine.

    Now, problem is, how I want it shown. I need them sorted on the report like so:

    Code:
    1  |    2   |    3
    those are three colums, below each, should be displayed it's dates according to the date id as it is in the table.

    if id is 1, put it under the first column, if it's 2, the second... etc, while doing it only with the rows recieved by the query.

    Will appreciate your help, as always, thank you so much.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What do you mean?
    Now, problem is, how I want it shown. I need them sorted on the report like so:


    Code:
    1 | 2 | 3


    those are three colums, below each, should be displayed it's dates according to the date id as it is in the table.

    if id is 1, put it under the first column, if it's 2, the second... etc, while doing it only with the rows recieved by the query.
    is ambiguous ... how do those 3 columns relate to your data? Is is each record? Summarized totals? what?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    I have a table:

    Code:
    ID  |      DATE      | DATEID
    1   |  01/01/2005  |    1
    2   |  23/12/2004  |    2
    3   |  11/05/2004  |    3
    4   |  24/04/2005  |    2
    That's my table.
    It contains all dates, from each year, month, and whatsoever.

    Now, I create a report using a query which holds only the dates from a certain month, so let's say I get 30 dates, all from october, until here - all fine.

    Now, I need to show them on the report. showing them all at once is not a problem, but I want to show them each below his DATEID. So:

    Code:
    DATEID: 1    |      DATEID: 2         |        DATEID: 3
       all dates   |  all dateid=2 dates   |    all dateid=3 dates
    Got the idea?

    Thanks!

  4. #4
    Join Date
    Dec 2004
    Posts
    41
    Sorry for jumping this up, but I still have no solution.

    I managed to do it with subreports, but for some reason the report creates a subreport for each row in the query, so I get the right data, but it is displayed over and over again in upto 20 pages, where 20 can be changed depending on how many rows were recieved from the query.

    Simple question: How do I display a subreport on a report, only once?
    I have the subreport working, it's just being fired too many times for some reason.

    Thank you all

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Generally this would be a crosstab query & report ... But what you could also do is read your records and bucketize them according to the dateid (where you write out each record you read in it's appropriate column) ... I have posted code for this previously ... But can post again if needed.
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Dec 2004
    Posts
    41
    Yes I can use such a code, appreciate if you post some example.

    Thank you

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This is ONLY an example... You'll have to extract the concept and apply to your situation (3 buckets should do) ...

    Code:
    Function RetrieveBillingDetail() As Integer
        On Error GoTo Err_RBD
        
        Dim BillMonth As Integer, lcv As Integer
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        ' Clear any previous report values
        SQLString = "DELETE FROM " & RptTbl & ";"
        CurrentProject.Connection.Execute SQLString, , adCmdText
        
        ' Retrieve the Dacor Billings
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, [WO Billing Detail].LaborCharged, [WO Billing Detail].PurchasesCharged, [WO Billing Detail].[Billed Cost], [WO Billing Detail].[Billing Date]"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN [WO Billing Detail] ON [Work Orders II].WorkOrderNumber = [WO Billing Detail].WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',[WO Billing Detail].[Billing Date]))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        
        ' Retrieve the Cash Payments
    'SELECT [Work Orders II].ContactID, CustomerContacts.[First name], CustomerContacts.MI, CustomerContacts.[Last name], Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate
    'FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber;
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',Payments.ReceivedDate))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        Set TrgRecSet = Nothing
        LoadingDelay
        
        RetrieveBillingDetail = 1
    Exit_RBD:
        Exit Function
        
    Err_RBD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveBillingDetail = 0
        Resume Exit_RBD
    End Function
    ... This vba code is run from WITHIN the report itself ... There is no query that the report is designed on ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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