Frustrating report display problem

[stevekos07]

Hello all. This is one of those frustrating things which should be simple but just isn't.

I have a report based on (I think!) a query. One of the fields is [FullName], which I always compile after sorting LastName as Ascending. Therefore any time FullName is used it ought to be sorted alphabetically in ascending order by last name. ?? When ever I run a query it behaves as it should, but not necessarily in a report.

For some reason in this report the FullName field is sorting by First name. When I try to access the source of the control all it tells me is the field name from the source, not the path to the source, so it could be one of a number of queries with FullName as a calculated field. (I forget which query was the source for the report!)

1. How do I find out which object is the source for the field?
2. How do I make this field sort by last name in the report view?
3. Are there any insider tricks to getting reports to display data as it should, or am I missing something fundamental?

 

[MarkK]

Open the report in design view and click the little square where the two rulers join (upper left) to select the report itself. Then open the property sheet and consult the first property on the Data tab, called Record Source. That is the query that provides data to the report.

Then, click a control and check the same location, the first property on the Data tab for the "Control Source" property. This is the name of the field from the Record Source, above, that will display in that control.

To group and sort in a report, there is a toggle button on the Report Design Tools ribbon, Design tab, Grouping & Totals group, called Group & Sort. Click that button and you toggle the visibility of a panel that enables you to add grouping levels and sorting to your report.

Hope this helps,

 

[stevekos07]

Thanks MarkK. I sorted it out myself by copying the SQL to a text editor and after looking at it carefully noticed that FullName was there but as a calculated field, not a selected one. I just changed the ORDER BY statement to that field name and all worked ok. I don't remember setting this field as a calculated field, it must have applied the SQL from the source query but I had a hard time figuring out how to fix it from that end.

Cheers for your advice.

 

[spikepl]

Don't get surprised if you get new surprises. Reports have their own outlook on things and do not follow the ordering set in SQL. Follow MarkK's advice to be sure.