Solved Unable to sort a report using a hidden field. (1 Viewer)

progrob

New member
Local time
Today, 01:02
Joined
May 5, 2021
Messages
4
How do I sort a report using a feild that does not display in the report?

I'm a new user and have worked out my table and query. The problem I'm having is the report needs to be sorted in sequence. The query sequences the records correctly but my report does not.

I have been able to get my report to sort correctly using a field.

I do not want that field to show on the report. How do I do this? (In other-words how do I sort a report using a field that does not display on the report?)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:02
Joined
Oct 29, 2018
Messages
21,358
Hi. The field to sort by doesn't have to be displayed. As long as it's included in the Record Source of the report, you can select it from the dropdown in the Sorting and Grouping section of the report.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:02
Joined
Sep 21, 2011
Messages
14,050
Just set the control visible property to false?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:02
Joined
Feb 19, 2002
Messages
42,981
Access knows it is smarter than we are and so sometimes it "helps" us. In reports, if you don't bind a field of the RecordSource to a control (the control doesn't have to be visible), Access removes the extra fields from the RecordSource for "efficiency". Therefore, if you want to sort on a field, you must bind it to a control but as Gasman mentioned, the control does not have to be visible. Note, that as part of this query rewrite, Access also ignores any sort you have in your query. You need to use the report's sorting/grouping options to sort the report.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:02
Joined
Oct 29, 2018
Messages
21,358
Access knows it is smarter than we are and so sometimes it "helps" us. In reports, if you don't bind a field of the RecordSource to a control (the control doesn't have to be visible), Access removes the extra fields from the RecordSource for "efficiency". Therefore, if you want to sort on a field, you must bind it to a control but as Gasman mentioned, the control does not have to be visible. Note, that as part of this query rewrite, Access also ignores any sort you have in your query. You need to use the report's sorting/grouping options to sort the report.
Interesting. I've never run into this, so I wanted to check. I created a report based on a table with six fields in it. I only put the first three fields on the report and sorted the report based on the sixth field. I saved and opened the report again. In design view, the record source didn't change. When I use the immediate window to check the record source, it also returned all the fields from the table. How can I see this behavior? Or, is there any link available to read more about it? Thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:02
Joined
Feb 19, 2002
Messages
42,981
Make sure you close the report object and the database. The recordsource isn't changed immediately. You might have to compact also. I don't remember at what point Access performs it's magic.

Access doesn't change what you see as the RecordSource. If you have a table or a query, it preserves that but it doesn't use it. I usually run into the problem because I write code referencing some field in the RecordSource that isn't bound to a control. The code works. I save the report and move on. Some time later, the report fails with a compile error because the referenced field no longer exists.

Keep in mind that this "feature" has been around for over 20 years and it might be that the Access team is finally getting around to removing it or getting smarter about it's rewritten RecordSource.

I think I once used the SQL tools to view queries that were sent to the server to prove to myself that columns that were not bound were not selected.

Try this. bind three fields and leave three unbound. Save, close, compact. Add code to the on Format event and put a stop on that line. When the code stops, see if you can reference the unbound fields in the debug window.

? Me.fld4
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:02
Joined
Sep 21, 2011
Messages
14,050
@Pat Hartman and if I recall correctly, you have said that this does not happen with forms and you can refer to fields not bound to any control?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:02
Joined
May 7, 2009
Messages
19,169
Access doesn't change what you see as the RecordSource. If you have a table or a query, it preserves that but it doesn't use it.
never heard of this.
i can use the Format event of the detail section to see if any fields are still intact, No Magic, all is there.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:02
Joined
Sep 21, 2011
Messages
14,050
OK, I am intigued about this.
I created a report based on a table.
I deleted the TransactionDate control, added a sort on TransactionDate DESC and saved and closed the DB.
I opened the DB and did a C&R as I forgot that step. :(

Report works and displays descending by date. Did another C&R and closed and reopened the DB, report still performs as required.

So no idea as to why the O/P is having problems :(

Edit: Having reread the first post it appears the O/P is unaware that any sort prior to the report is ignored and has to be done in the Report's own Sort and Grouping as per the DBGuys statement in post 2..

@progrob Have you actually done that?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:02
Joined
Feb 19, 2002
Messages
42,981
Here is a sample. The field AuthCode is in the query but not bound to a control. I added a messagebox to display it in the Print event of the report's footer. Access can't find the field.

Remember, Access doesn't rewrite the query immediately. I haven't pinpointed when this happens. I think perhaps the next time the report is opened in design view. I've made this mistake a number of times and for a while the report seems to work and then it doesn't. Probably because I opened it in design view. I'll send it to MS. Maybe they can work it out.

I added a second copy. The first one might be corrupt
 

Attachments

  • ReportRecordSourceWithError.zip
    50.5 KB · Views: 117
  • ReportRecordSourceWithError2.zip
    48 KB · Views: 122
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 08:02
Joined
Sep 21, 2011
Messages
14,050
Well if it is not in the actual report, I would expect that one could not display/refer to it, but in the sort/group options it appears to be available and works?

If I take your second DB and set it as
1620497301456.png

and comment out that msgbox, then the report sorts as I would expect it to from that graphic, despite the fact Authcode is nowhere on the report.?

FWIW I am only reporting what I am finding out.?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:02
Joined
Feb 19, 2002
Messages
42,981
I'm not looking for a fix. I know how to fix the problem the example shows. I bind AutoCode to a hidden control and the code will work without error.

I showed you the error. What you did must have prompted Access to include AuthCode in its version of the query.

As you can see, if you open the query, you see the field. The example shows that even though the field is in why I think is the RecordSource, Access disagrees.
 

Users who are viewing this thread

Top Bottom