Exporting report to individual PDF's

[MarkK]

Here's wishing you a speedy recovery!
Mark

 

[Velox]

Thanks for your kind words! Been on them a week now and I think i'm adjusting now as i'm able to think again without getting a headache...

I've been working on this again and just wondered if anyone would be able to tell me why adding the below "ORDER BY Date " clause to the SQL query gives "ORDER BY clause (Date) conflicts with DISTINCT". I am guessing that it's down to not specifically adding a query to search for date but after several hours of head scratching I've been unable to get a workaround.

I tried removing the distinct and tried to group it instead but couldn't make that work either. I'm just trying to get the entries in the PDF's to show in date order as they're currently random.

I also tried adding a clause to the record source to order it but whilst that doesn't cause any errors, it doesn't actually do anything!

Any thoughts?

Current record source: SELECT * FROM [Remote Support] WHERE Customer = TempVars!Customer;

Option Compare Database

Sub PDF_Export()
    Const RN As String = "Remote Support Report"
    Const PATH As String = "C:\Temp\Remote\"
    Const SQL As String = _
        "SELECT DISTINCT Customer " & _
        "FROM [Remote Support Query] " & _
        "ORDER BY Date "
        
    With CurrentDb.OpenRecordset(SQL)

        Do While Not .EOF
            ' set the value of the TempVar the report will expect
            TempVars!Customer = !Customer.Value
                        
            ' output the report to pdf
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"
            .MoveNext
        Loop
        .Close
    End With

        
End Sub

 

[MarkK]

Well, there's no point sorting the report because it only ever prints a single row at a time. Sorting a single row never makes sense.

As far as the error goes, the DISTINCT operator rolls many rows into one, so then it makes sense that specifying the date is ambiguous. The date from which Customer row?

I think the solution is an aggregate, or GROUP BY, query, like...

    Const SQL As String = _
        "SELECT q.Customer " & _
        "FROM RemoteSupportQuery As q " & _
        "GROUP BY q.Customer " & _
        "ORDER BY Max(q.Date) "

...and I've 'aliased' the query name to ensure that the field 'Date' is never confused as a SQL datatype, or the VBA function with the same name. Also, you can see that we use the Max() function which, for each Customer selected, selects the largest of the available dates. You could also use Min() there, or you could even Avg() those dates if that made most sense.
hth
Mark

 

[Velox]

So you don't have to define the date as well if your using it in the query? Like:

    Const SQL As String = _
        "SELECT q.Customer, [B]q.Date[/B] " & _
        "FROM [Remote Support Query] As q " & _
        "GROUP BY q.Customer " & _
        "ORDER BY Max(q.Date) "

I've probably completely misunderstood. Obviously adding the above causes an error but it was just an example.

The original additional code gets through the debugger and still prints out the pages but doesn't actually seem to do anything in addition to the original code. The attached PDF is one of the outputs from running this and you can see goes earliest date, latest date then middle date. I also tried the Min & Avg options but they don't make any changes to the ordering at all.

I've been ploughing through this link but have yet to shed any light on where this is going wrong. I'm keeping on ploughing though!

I thought I was onto something with the ordering as it first appeared to be ordering it by time & hours but I went through and changed all the time / hours values for that customer and the dates remained in the same order so I've crossed that off!

Option Compare Database

Sub PDF_Export()
    Const RN As String = "Remote Support Report"
    Const PATH As String = "C:\Temp\Remote\"
    Const SQL As String = _
        "SELECT q.Customer " & _
        "FROM [Remote Support Query] As q " & _
        "GROUP BY q.Customer " & _
        "ORDER BY Max(q.Date) "

        
    With CurrentDb.OpenRecordset(SQL)

        Do While Not .EOF
            ' set the value of the TempVar the report will expect
            TempVars!Customer = !Customer.Value
                        
            ' output the report to pdf
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF, PATH & !Customer & ".pdf"
            .MoveNext
        Loop
        .Close
    End With

        
End Sub

 

[MarkK]

So you don't have to define the date as well if your using it in the query? Like:

    Const SQL As String = _
        "SELECT q.Customer, [B]q.Date[/B] " & _
        "FROM [Remote Support Query] As q " & _
        "GROUP BY q.Customer " & _
        "ORDER BY Max(q.Date) "

It would have to be...

        "SELECT q.Customer, [B]Max(q.Date) As Date[/B] " & _

...but we don't need that data as a field in the query. We are just using that to sort the Customers by date, which is how I understood your request. To test it, paste this into a new query...

SELECT q.Customer, Max(q.Date) As [Date] FROM [Remote Support Query] As q GROUP BY q.Customer ORDER BY Max(q.Date)

...and see if the 'Date' field is correctly sorted.

As to the rest of your post, I don't really see a question or something else to respond to. Did you mean to ask something? Does the posted code fail? How?
Mark

 

[Velox]

Sorry, I wasn't clear, this:

    Const SQL As String = _
        "SELECT q.Customer  " & _
        "FROM [Remote Support Query] As q " & _
        "GROUP BY q.Customer " & _
        "ORDER BY Max(q.Date) "

...does not appear to do anything noticeably different to this:

    Const SQL As String = _
        "SELECT DISTINCT Customer " & _
        "FROM [Remote Support Query]

at least as far as the exported PDF goes. They both print the support entries on the PDF's in exactly the same order and I'm currently failing to see why. I will try and test your suggestion tomorrow to see what occurs.

Thanks for the update!