CODEWITHSUNDEEP
sqlvbams-accesssql-order-by
agentkit office2
agentkit office2

Reputation: 41

DoCmd.OpenReport and ORDER BY in the report

I have a report Email in MS Access and it is now reporting all the sales of the real state. In the report we have the ID, Address, Suburb, Sale Price, Land, Beds, Valuation and Selling Agency.

The code is working now but I want to order it by the SUBURB. I tried to put it in the WhereCondition, but does not work.


qry = qry & ") And [COND DATE] >= CDate(""" & Me.txtDateStart2.Value & """)" & " And [COND DATE] <= CDate(""" & Me.txtDateEnd2.Value & """) ORDER BY [SUBURB]"
DoCmd.OpenReport "rptStageThree", acViewReport, , qry
DoCmd.OutputTo acReport, "rptStageThree", "MicrosoftExcelBiff8(*.xls)", CurrentProject.Path & "\" & suburpsCol(1).Contact & ".xls", False, "", 0

I still trying with other options that I found here, like using the OpenArgs arguments in the DoCmd.OpenReport:

qry = qry & ") And [COND DATE] >= CDate(""" & Me.txtDateStart2.Value & """)" & " And [COND DATE] <= CDate(""" & Me.txtDateEnd2.Value & """)"
DoCmd.OpenReport "rptStageThree", acViewReport, , qry, , "ORDER BY [SUBURB]"
DoCmd.OutputTo acReport, "rptStageThree", "MicrosoftExcelBiff8(*.xls)", CurrentProject.Path & "\" & suburpsCol(1).Contact & ".xls", False, "", 0

This is all my code:


Private Sub EmailProofToOffices_Click()
Dim qry As String
Dim col As New Collection
Dim suburpsCol As Collection
Dim db As DAO.Database
Dim rsEdit As DAO.Recordset
Dim lItem As Long
Dim item
Dim OfficeCentralItem  As OfficeCentralReportsItem

Dim OutApp As Object
Dim OutMail As Object

    For lItem = 0 To Me.List40.ListCount - 1
        If Me.List40.selected(lItem) Then
            col.Add Me.List40.ItemData(lItem)
        End If
    Next lItem

        For Each item In col
            Set db = CurrentDb
            Set rsEdit = db.OpenRecordset(Constants.OfficeCentralTHOMPSONTABLE)
                'rsEdit.MoveFirst
            Set suburpsCol = New Collection
                 Do While Not rsEdit.EOF

                     If Trim(item) = Trim(rsEdit.Fields("Office").Value) Then
                         Set OfficeCentralItem = New OfficeCentralReportsItem
                         OfficeCentralItem.Suburbs = rsEdit.Fields("Suburbs").Value
                         OfficeCentralItem.EmailAddress = rsEdit.Fields("Email Address").Value
                         OfficeCentralItem.Contact = rsEdit.Fields("Contact").Value
                         suburpsCol.Add OfficeCentralItem
                     End If
                rsEdit.MoveNext
                Loop
                If suburpsCol.Count > 0 Then
                    If Me.txtDateStart2.Value <> "" And Me.txtDateEnd2.Value <> "" Then
                        If Not IsNull(Me.txtDateStart2.Value) And Not IsNull(Me.txtDateEnd2.Value) Then
                                qry = ""
                            For Each OfficeCentralItem In suburpsCol
                                If qry = "" Then
                                    qry = qry & "([SUBURB]= " & """" & OfficeCentralItem.Suburbs & """"
                                Else
                                    qry = qry & "Or [SUBURB]= " & """" & OfficeCentralItem.Suburbs & """"
                                End If
                            Next
                                qry = qry & ") And [COND DATE] >= CDate(""" & Me.txtDateStart2.Value & """)" & " And [COND DATE] <= CDate(""" & Me.txtDateEnd2.Value & """)"
                                DoCmd.OpenReport "rptStageThree", acViewReport, , qry
                                DoCmd.OutputTo acReport, "rptStageThree", "MicrosoftExcelBiff8(*.xls)", CurrentProject.Path & "\" & suburpsCol(1).Contact & ".xls", False, "", 0

                                  'Email Part
                                    Set OutApp = CreateObject("Outlook.Application")
                                    Set OutMail = OutApp.CreateItem(0)

                                        With OutMail
                                            .Display
                                            .To = suburpsCol(1).EmailAddress
                                            .Subject = sTitle
                                            Body = .HTMLBody
                                            '.HTMLBody = "<table><tr>Hi  " & suburpsCol(1).Contact & ",</tr><tr/><tr/><tr>Please find the attached spreadsheet containing records of sales in your group of suburbs for " & Me.txtDateStart2.Value & " to " & Me.txtDateEnd2.Value & ".</tr><tr/><tr/><tr>Proof data now includes details of Sale Price %/Valuation, List Date and Days on Market.  This will not be included in final reports but may be useful to quickly identify sales where the details returned to REINZ contains errors.</tr><tr/><tr/><tr>Could you please confirm if this data is approved for use or if you have any changes.  Where no response is received within 3 business days final PDF reports and graphs will be built with the data as is.  Changes are not possible after deadline for technical reasons.</tr><tr/><tr/><tr>Many thanks,</tr><tr>Aaron and team.</tr></table>"
                                            .Attachments.Add CurrentProject.Path & "\" & suburpsCol(1).Contact & ".xls"
                                        End With
                                    Set OutMail = Nothing
                                    Set OutApp = Nothing
                        End If
                    End If
                End If
           Set suburpsCol = Nothing
           Set rsEdit = Nothing
           Set db = Nothing
        Next
End Sub

How do I add on to that syntax to put in the Order By criteria? Can someone help me?

Thanks in advance.

Upvotes: 3

Views: 2796

Answers (1)

ComputerVersteher
ComputerVersteher

Reputation: 2696

The WhereCondition argument of.OpenReportjust provides a filter string, you can't sort there.

For some reason, Ms Access reports ignore sort order in their record sourceSQL. To change Order By for report data you have to useGroup & SortonDesign Viewor set theOrderByproperty inReport_Openevent, e.g with theOpenArgsargument ofDoCmd.OpenReport:

Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
End If
End Sub

Open your report with:

DoCmd.OpenReport "rptStageThree", acViewReport, , qry, , "[SUBURB] ASC" ' "[SUBURB] DESC" for sort descending, multiple fields seperated by comma "[Suburb], [Sale Price] DESC, [Land]"

Upvotes: 3

Related Questions