We help IT Professionals succeed at work.

Problems with a descending sort applied to a report in MS ACCESS VBA

966 Views
Last Modified: 2014-09-02
I have a print sort tab on a multitab form.  I can sort the records the way I want, and the report will produce an output that matches the sort, but if I add a "desc" after the field I want to sort on I get an error that says
"Enter parameter value  then shows the fieldname with the desc after it.

How do I get descending to work?

Thanks
Comment
Watch Question

Author

Commented:
Here is the code.  I tried to add a wherecondition to the openreport but I don't really understand how to make it work.
The sort works just fine and is then stored in the OrderBy attribute of the form.

Private Sub cmdPrintReportNoPhotos_Click()
On Error GoTo Err_cmdPrintReportNoPhotos_Click
'name the report
    Dim stDocName As String
    stDocName = "ReportPage1NoPhotos"
    'Dim Where As Variant ' this is for the sorting.
    'Where = "SELECT WetForm.*, WetVeg.* FROM WetForm INNER JOIN WetVeg ON WetForm.ID1=WetVeg.ID2 Order by " & Me.OrderBy & ";"
'Identify the filter items
    Dim rptFilter As String
    rptFilter = " "
    If cboProjectSelect <> "All" Then
    rptFilter = "ProjectSite = " & Chr(34) & cboProjectSelect & Chr(34)
    End If
    If cboAppSelect <> "All" Then
        If rptFilter = " " Then
            rptFilter = "ApplicantOwner = " & Chr(34) & cboAppSelect & Chr(34)
        Else
            rptFilter = rptFilter & " and ApplicantOwner = " & Chr(34) & cboAppSelect & Chr(34)
        End If
    End If
    If cboInvestSelect <> "All" Then
    If rptFilter = " " Then
    rptFilter = "Investigator = " & Chr(34) & cboInvestSelect & Chr(34)
    Else
    rptFilter = rptFilter & " and Investigator = " & Chr(34) & cboInvestSelect & Chr(34)
    End If
    End If
    If cboStartSelect <> "All" Then
    If rptFilter = " " Then
    rptFilter = "SDate >= " & "#" & cboStartSelect & "#"
    Else
    rptFilter = rptFilter & " and SDate >= " & "#" & cboStartSelect & "#"
    End If
    End If
    If cboEndSelect <> "All" Then
    cboEndSelect = cboEndSelect + 1
    If rptFilter = " " Then
    rptFilter = "SDate <= " & "#" & cboEndSelect & "#"
    Else
    rptFilter = rptFilter & " and SDate <= " & "#" & cboEndSelect & "#"
    End If
    End If

'now print based on whether or not there is a filter
If rptFilter = " " Then ' no filter
        If txtStartRec = "All" And txtEndRec = "All" Then
            DoCmd.OpenReport stDocName, acViewNormal
        Else
            DoCmd.OpenReport stDocName, acViewPreview
            DoCmd.PrintOut acPages, txtStartRec * 2 - 1, txtEndRec * 2
        End If
    Else 'there is some filter
        If txtStartRec = "All" And txtEndRec = "All" Then
            DoCmd.OpenReport stDocName, acViewNormal, rptFilter
        Else
            DoCmd.OpenReport stDocName, acViewPreview, , rptFilter
            DoCmd.PrintOut acPages, txtStartRec * 2 - 1, txtEndRec * 2
        End If
    End If

Exit_cmdPrintReportNoPhotos_Click:
    Exit Sub

Err_cmdPrintReportNoPhotos_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintReportNoPhotos_Click
End Sub
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the help.  you made it clear that I can't control the report from the form except for filtering.  So I controlled the report from the report instead using an on open event that called the sorting info from the Form.

First I had to remove all of the groupings that were built into all the reports. You can see these by getting into design mode then Design>Group&Sort then remove all of the existing groups.  

Then replace the On Open event for the report with this:
Private Sub Report_Open(Cancel As Integer)
'This sets up this sorting order for printing by calling the sorting fields from the form.
rptSort = Forms![routinewetlanddelineation].[cboSort1] & ", " & Forms![routinewetlanddelineation].cboSort2 & ", " & Forms![routinewetlanddelineation].cboSort3 & ", " & Forms![routinewetlanddelineation].cboSort4 & ", " & Forms![routinewetlanddelineation].cboSort5
'This orders the report directly from inside the report.
OrderBy = rptSort
Me.OrderByOn = True
End Sub
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"You can filter on OpenReport but not sort"

Umm ... that's not the case Gustav. I'm been doing that for years by setting the OrderBy  Property in the Report OnOpen event.

mx

Author

Commented:
Yes, but to set it interactively from a form, you have to send some variables to the report for the report to use as the Orderby setting.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, I meant the filter parameter on the command line.

/gustav

Author

Commented:
For some reason filtering works without any problem from the Form, only the sorting, using the "desc" suffix caused the problem at first.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Seems to be a lot of confusion here.

IF ... the Grouping & Sorting dialog has been used for Grouping, then Access will completely ignore any sorting in the underlying Recordsource of the Report.
IF ... the Grouping & Sorting dialog has not been used, then Access will honor any sorting present in the underlying query (assuming a query and not just a table).

If there is no sorting specified in the query, the query (hence the Report)  will be sorted by the Primary Key of the Table the query is based on. If there is no Primary Key in the table, then the query will be sorted by the order the records were entered in the table.
IF there is more than one table in the query, then it will be harder to determine what the sorting is ... again, all of this IF ... the Grouping & Sorting dialog has not been used.

Further ... if the G&S dialog is used AND there is sorting specified in the query, Access will still do the query sorting, adding a performance hit (may or may not be noticeable). I've never heard of Access 'rewriting' the query and changing it to a Snapshot recordset.  In fact, a Snapshot recordset will generally be slower that a dynaset recordset because Access supposedly has to read the entire recordset upfront to get the 'latest' data, so I seriously doubt that is happening. I never use Snapshot recordsets for anything.

mx

Author

Commented:
AND,
If there is not G&S that is preset for the report,
And
If you want to change the sort order of the Report using a Form, you can select the fields you want to use for sorting by using controls on the Form that are populated with the field lists.  You can also add the text "desc" to the field name while it is in the control to get a descending sort.
THEN
You can call the items in the controls on the Form to fill in the Orderby property of the report.

This will be a nice reference for the future.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"If there is not G&S that is preset for the report, "

Even if there is,  any 'preset' Grouping and/or Sorting will be overridden if set in code in the Report Open Event.

mx

Author

Commented:
I did not find that to be true I don't think.  If I set the orderby to something different than the preset, it ignored the orderby.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
And RE this what Pat said:

"  So, if you've ever run into a problem with code in a report because it couldn't resolve a field, this is the reason.  If you want to use a field in code, you MUST bind it to a control on the report."

There is one other piece to that.  If you do bind a Control to a Field in the query AND you want to refer to it in code, you MUST make the Name property of the Control different than the Control Source. Access defaults the Name of a bound Control to the same name as the bound field.

So, if your Control Source is say
CompanyName
The Name of the Text box holding this field would need to be something like
txtCompanyName ... again, ONLY if you need to refer to that Field in code.

Author

Commented:
Good to know, in my case that is what I did with the cbo (combobox) prefix.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"I did not find that to be true I don't think.  If I set the orderby to something different than the preset, it ignored the orderby."

I've been doing it for years ... again, setting the OrderBy property in the Report Open event.

btw ... a side note.  Whereas Sorting in a query that drives a Report is ignored per what I stated above, Grouping is not of course.  Sometimes it's convenient or necessary to do some 'pre grouping' in the query.

Author

Commented:
Sorry to contradict you, but replacing the orderby does NOT work unless all the preset order&sort in the report has been removed.  I produced a video to demonstrate that fact and you can get to it at:
http://www.Ecotone.org/Download/SortDemo.mp4.txt   just remove the .txt to run the mp4
It is about 49MB in size.

Or try http://www.Ecotone.org/Download/SortDemo.html for a flash version.  About 7 MB, just click the green button in the center after it downloads.  There is a bit of a delay after you click the green button.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Joe,
In order to see the impact of the changes to the query, you need to be linked to SQL Server and you need to view the query that is sent to the server.  It is possible that newer versions of Access don't force a snapshot.  I haven't checked in a while but 2003 and possibly 2007 did force a snapshot.
To get a partial sense of the changes, add a column to the query that is not bound to any control in the report.  Then add code that references the column.  It will work initially but after you save and reopen the report, it will fail.
Pat
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
" you need to be linked to SQL Server and you need to view the query that is sent to the server"
:-(
I was only referring to Access dbs.

Yes ... I agree you cannot ... from code ... reference a Field that is not actually on the Report.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I don't think it works differently.  It is just that you can see the damage if you are linked to SQL Server because you actually get to see the result of the changes if you look at the query as it is sent to the server.  I don't know of any way to see the changes made by the report engine if the BE is Jet/ACE.  They are not saved anywhere and they don't permanently change the RecordSource query.  The change only exists while the report runs.  The only way to verify that Access is indeed mucking with your query is to do what I suggested and use a column in code that isn't bound to a control.  Questions like this can easily be answered by the Access team if only they were willing.

I forgot to mention the name issue.  I never run into it since the first thing I ever do when I create a form/report is to rename all the Name properties to be different from the bound column name.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.