Sort not working correctly

[Frank]

I created a report for a query. When I sort the desired field most of the
records sort correctly, but some records do not. This phenomenon happened
both in the query and the report. I was able to solve the query sort problem
by highlighting the column in the data set view and right click ascending.
The mixed sort continues in the report weather I use Order By On or the
Sorting and Grouping dialog box. Any idea what I'm doing wrong?

 

[Marshall Barton]

I created a report for a query. When I sort the desired field most of the
records sort correctly, but some records do not. This phenomenon happened
both in the query and the report. I was able to solve the query sort problem
by highlighting the column in the data set view and right click ascending.
The mixed sort continues in the report weather I use Order By On or the
Sorting and Grouping dialog box.

You need to use the same sorting in the report's Sorting and
Grouping that you used in the query. Note that sorting a
report's record source query is a waste of time because
anything in Sorting and Grouping overrides whatever was done
in the query.

Since you said you already tried using Sorting and Grouping,
you should post the sorting that worked in the query along
with the Sorting and Grouping that didn't work so we can see
the information that relates to the question.

 

[Frank]

Marsh:

Thank you for your response. What didn't work in the query mode was using
the sort ascending function in the Design View. What worked in the query
mode was highlighting the column in the Datasheet View, right click the mouse
and choose Sort Ascending.

In the Design View of the Report, in the Sorting and Grouping dialog box I
chose chrCompany in the Field/Expression and Ascending. Group Header No,
Group Footer No, Group On Each Valve, Group Interval 1, Keep Together No.

When this didn't work I deleted the Sorting and Grouping and went to the
Properties dialog box and changed the Order By On to Yes, to see if it would
keep the proper order from the source query. It didn't work. I have gone
back to the Sorting and Grouping with the same poor results.

In this query it returns a total of 34 records. The first 24 records are in
alphabetical order starting with C and ending with U. The next 8 records are
in alphabetical order starting with Q and ending with S. The last two
records start with E and C.

I have 15 reports which are all sourced from 15 different queries (basically
15 people needing 15 different sorts of the same database). They are all
acting the same way. I have gone through each report and discovered that the
same names keep turning up out of logical order.

 

[Marshall Barton]

Sorry, but your descriptive text is too vague to communicate
the details needed to understand the problem. When talking
about a query, the query design grid is just too difficult
to describe so the best way to convey it is to post a
Copy/Paste of query's SQL view. If you could not get the
query records as displayed in the query's datasheet view
sorted correctly, then the query can be used to debug the
problem without complicating thing by involving the report.

I have never heard of, much less seen, the kind of result
you are describing so I have to conclude there is more going
on than what you have posted. For example, maybe the
chrCompany field values have some number of spaces (or other
unprintable characters) in front of the name. Except got
the null character, chr(0), his particular hypothesis can be
tested by adding a calculated field
Test: "[" & chrCompany & "]"
and checking if there is any space between the name and the
brackets.

Note 1) When you use the shortcut menu to sort the selected
column, it (temporarily) changes the query's Order By clause
so it should(?) have the same effect as using the Order By
clause in the original query.

Note 2) Using the report's OrderBy property will also
override the query sort. But, it is almost as useless as
using the Order By clause in the query. The report's
OrderBy property will will be applied to the report's
dataset, but only after whatever is specified in Sorting and
Grouping.

As I said before, the only reliable way to sort the records
in a report is to use Sorting and Grouping. It's just a
waste of time to look for a solution using any other
approach. This is not to say that another approach can not
be used to debug the problem. The fact that you got a
different result using the shortcut menu sort is a clue of
some kind. However, what it means is beyond me unless you
are actually doing something different such as using a
Lookup field in the table and something else in the
query/report.

 

[Frank]

In an attempt to give you as much information as possible regarding the
origin of this field that I am trying to sort ascending I am including the
characteristics from the Table design view.

In the second part of the response I present 3 scenarios’ regarding the
query sort problem. Each scenario is accompanied by the SQL VIEW for that
query and the result.

All my queries use the table tblIncoming as their source. The field
entitled chrCompany is in this table. It is populated with information from
another table called tblCompanies. In the DESIGN VIEW of tblIncoming for the
field chrCompany the DATA TYPE is Number. In the field properties section
under the LOOK UP tab
Display Control: COMBO BOX
Row Source Type: Table/Query
Row Source: SELECT tblCompanies.idsID, tblCompanies.chrCompanyName FROM
tblCompanies ORDER BY [chrCompanyName];
Bound Column: 1
Column Count: 2
Column Heads: NO
Column Widths: 0â€;1â€
List Rows: 8
List Width: 1â€
Limit to List: YES

Scenario No. 1
No sort is selected in the DESIGN VIEW. In the DATA SHEET VIEW right click
chrCompany column and choose sort ascending.

Sort is correct

SQL View No. 1

SELECT tblIncoming.dtmDateReceived, tblIncoming.chrCompany,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblIncoming.chrPrimaryAction, tblIncoming.[chrSupportAction(1)],
tblIncoming.[chrSupportAction(2)], tblIncoming.[chrSupportAction(3)],
tblIncoming.[chrSupportAction(4)], tblIncoming.chrFollowUp,
tblOutgoing.chrSTATUS, tblIncoming.memComments

FROM tblIncoming LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]

WHERE (((tblIncoming.chrPrimaryAction)="2") AND ((tblOutgoing.chrSTATUS) Is
Null)) OR (((tblIncoming.[chrSupportAction(1)])="2") AND
((tblOutgoing.chrSTATUS) Is Null)) OR
(((tblIncoming.[chrSupportAction(2)])="2") AND ((tblOutgoing.chrSTATUS) Is
Null)) OR (((tblIncoming.[chrSupportAction(3)])="2") AND
((tblOutgoing.chrSTATUS) Is Null)) OR
(((tblIncoming.[chrSupportAction(4)])="2") AND ((tblOutgoing.chrSTATUS) Is
Null));


Scenario No. 2
In the DESIGN VIEW column chrCompany is set to sort ascending. In the DATA
SHEET VIEW the data is not sorted correctly.

The sort is not correct

SQL View No. 2

SELECT tblIncoming.dtmDateReceived, tblIncoming.chrCompany,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblIncoming.chrPrimaryAction, tblIncoming.[chrSupportAction(1)],
tblIncoming.[chrSupportAction(2)], tblIncoming.[chrSupportAction(3)],
tblIncoming.[chrSupportAction(4)], tblIncoming.chrFollowUp,
tblOutgoing.chrSTATUS, tblIncoming.memComments

FROM tblIncoming LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]

WHERE (((tblIncoming.chrPrimaryAction)="2") AND ((tblOutgoing.chrSTATUS) Is
Null)) OR (((tblIncoming.[chrSupportAction(1)])="2") AND
((tblOutgoing.chrSTATUS) Is Null)) OR
(((tblIncoming.[chrSupportAction(2)])="2") AND ((tblOutgoing.chrSTATUS) Is
Null)) OR (((tblIncoming.[chrSupportAction(3)])="2") AND
((tblOutgoing.chrSTATUS) Is Null)) OR
(((tblIncoming.[chrSupportAction(4)])="2") AND ((tblOutgoing.chrSTATUS) Is
Null))

ORDER BY tblIncoming.chrCompany;



Scenario No. 3
In the DESIGN VIEW column chrCompany is set to sort ascending. In the DATA
SHEET VIEW the data is not sorted correctly. Then In the DATA SHEET VIEW
right click chrCompany column and choose sort ascending.

The sort is now correct.

SQL View No. 3

SELECT tblIncoming.dtmDateReceived, tblIncoming.chrCompany,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblIncoming.chrPrimaryAction, tblIncoming.[chrSupportAction(1)],
tblIncoming.[chrSupportAction(2)], tblIncoming.[chrSupportAction(3)],
tblIncoming.[chrSupportAction(4)], tblIncoming.chrFollowUp,
tblOutgoing.chrSTATUS, tblIncoming.memComments

FROM tblIncoming LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]

WHERE (((tblIncoming.chrPrimaryAction)="2") AND ((tblOutgoing.chrSTATUS) Is
Null)) OR (((tblIncoming.[chrSupportAction(1)])="2") AND
((tblOutgoing.chrSTATUS) Is Null)) OR
(((tblIncoming.[chrSupportAction(2)])="2") AND ((tblOutgoing.chrSTATUS) Is
Null)) OR (((tblIncoming.[chrSupportAction(3)])="2") AND
((tblOutgoing.chrSTATUS) Is Null)) OR
(((tblIncoming.[chrSupportAction(4)])="2") AND ((tblOutgoing.chrSTATUS) Is
Null))

ORDER BY tblIncoming.chrCompany;

Thank you for your patience.

--
Regards,
Frank

Sorry, but your descriptive text is too vague to communicate
the details needed to understand the problem. When talking
about a query, the query design grid is just too difficult
to describe so the best way to convey it is to post a
Copy/Paste of query's SQL view. If you could not get the
query records as displayed in the query's datasheet view
sorted correctly, then the query can be used to debug the
problem without complicating thing by involving the report.

I have never heard of, much less seen, the kind of result
you are describing so I have to conclude there is more going
on than what you have posted. For example, maybe the
chrCompany field values have some number of spaces (or other
unprintable characters) in front of the name. Except got
the null character, chr(0), his particular hypothesis can be
tested by adding a calculated field
Test: "[" & chrCompany & "]"
and checking if there is any space between the name and the
brackets.

Note 1) When you use the shortcut menu to sort the selected
column, it (temporarily) changes the query's Order By clause
so it should(?) have the same effect as using the Order By
clause in the original query.

Note 2) Using the report's OrderBy property will also
override the query sort. But, it is almost as useless as
using the Order By clause in the query. The report's
OrderBy property will will be applied to the report's
dataset, but only after whatever is specified in Sorting and
Grouping.

As I said before, the only reliable way to sort the records
in a report is to use Sorting and Grouping. It's just a
waste of time to look for a solution using any other
approach. This is not to say that another approach can not
be used to debug the problem. The fact that you got a
different result using the shortcut menu sort is a clue of
some kind. However, what it means is beyond me unless you
are actually doing something different such as using a
Lookup field in the table and something else in the
query/report.
--
Marsh
MVP [MS Access]

Thank you for your response. What didn't work in the query mode was using
the sort ascending function in the Design View. What worked in the query
mode was highlighting the column in the Datasheet View, right click the mouse
and choose Sort Ascending.

In the Design View of the Report, in the Sorting and Grouping dialog box I
chose chrCompany in the Field/Expression and Ascending. Group Header No,
Group Footer No, Group On Each Valve, Group Interval 1, Keep Together No.

When this didn't work I deleted the Sorting and Grouping and went to the
Properties dialog box and changed the Order By On to Yes, to see if it would
keep the proper order from the source query. It didn't work. I have gone
back to the Sorting and Grouping with the same poor results.

In this query it returns a total of 34 records. The first 24 records are in
alphabetical order starting with C and ending with U. The next 8 records are
in alphabetical order starting with Q and ending with S. The last two
records start with E and C.

I have 15 reports which are all sourced from 15 different queries (basically
15 people needing 15 different sorts of the same database). They are all
acting the same way. I have gone through each report and discovered that the
same names keep turning up out of logical order.

 

[Marshall Barton]

In an attempt to give you as much information as possible regarding the
origin of this field that I am trying to sort ascending I am including the
characteristics from the Table design view.

In the second part of the response I present 3 scenarios’ regarding the
query sort problem. Each scenario is accompanied by the SQL VIEW for that
query and the result.

All my queries use the table tblIncoming as their source. The field
entitled chrCompany is in this table. It is populated with information from
another table called tblCompanies. In the DESIGN VIEW of tblIncoming for the
field chrCompany the DATA TYPE is Number. In the field properties section
under the LOOK UP tab
Display Control: COMBO BOX
Row Source Type: Table/Query
Row Source: SELECT tblCompanies.idsID, tblCompanies.chrCompanyName FROM
tblCompanies ORDER BY [chrCompanyName];
Bound Column: 1
Column Count: 2
Column Heads: NO
Column Widths: 0”;1”
List Rows: 8
List Width: 1”
Limit to List: YES

Ah HA! The evil Lookup Field feature strikes again :-(

With your above information, it's clear to me that
everything is behaving as it is supposed to behave. The
entire problem is in your expectations of what is actually
in tblIncoming's chrCompany field.

As far as I (and many others) are concerned, you should
never expose a table sheet view to users. Given that no one
except you will ever see the data in its raw form, there is
absolutely no reason to use that Lookup feature to obfuscate
a field's data value. This is one of the 10 Commandments at
http://www.mvps.org/access/

The best advice I can give you is to change the field
properties section under the LOOK UP tab from combo box to
text box so you can see what is really going on. After
doing that you will be able to see that the sorting is
correct for the field's number value.

To fix things up so you can sort your report, change the
query to include tblCompanies:

SELECT tblIncoming.dtmDateReceived,
. . .,
tblCompanies.chrCompanyName

FROM (tblIncoming
LEFT JOIN tblCompanies
ON tblIncoming.chrCompany = tblCompanies.idsID)
LEFT JOIN tblOutgoing
ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]

WHERE . . .

The query could then be sorted as you want by using:
ORDER BY tblCompanies.chrCompanyName

And the report can be sorted by using the chrCompanyName in
Sorting and Grouping.

 

[Frank]

Marsh:

As I'm sure you have heard many times before "You da Man"!!

Thank you for your help. The solution worked perfectly. Unfortunately for
me I have an additional 5 fields in my database that I have used the Evil
Look Up Field. These 5 fields all reference the same but different table
then the solution you gave me. With what you have shown me I will try to
rectify my database. If I can't solve the problem I will be back in the
discussion group asking more questions.

Thanks again,

--
Regards,
Frank

In an attempt to give you as much information as possible regarding the
origin of this field that I am trying to sort ascending I am including the
characteristics from the Table design view.

In the second part of the response I present 3 scenarios’ regarding the
query sort problem. Each scenario is accompanied by the SQL VIEW for that
query and the result.

All my queries use the table tblIncoming as their source. The field
entitled chrCompany is in this table. It is populated with information from
another table called tblCompanies. In the DESIGN VIEW of tblIncoming for the
field chrCompany the DATA TYPE is Number. In the field properties section
under the LOOK UP tab
Display Control: COMBO BOX
Row Source Type: Table/Query
Row Source: SELECT tblCompanies.idsID, tblCompanies.chrCompanyName FROM
tblCompanies ORDER BY [chrCompanyName];
Bound Column: 1
Column Count: 2
Column Heads: NO
Column Widths: 0â€;1â€
List Rows: 8
List Width: 1â€
Limit to List: YES

Ah HA! The evil Lookup Field feature strikes again :-(

With your above information, it's clear to me that
everything is behaving as it is supposed to behave. The
entire problem is in your expectations of what is actually
in tblIncoming's chrCompany field.

As far as I (and many others) are concerned, you should
never expose a table sheet view to users. Given that no one
except you will ever see the data in its raw form, there is
absolutely no reason to use that Lookup feature to obfuscate
a field's data value. This is one of the 10 Commandments at
http://www.mvps.org/access/

The best advice I can give you is to change the field
properties section under the LOOK UP tab from combo box to
text box so you can see what is really going on. After
doing that you will be able to see that the sorting is
correct for the field's number value.

To fix things up so you can sort your report, change the
query to include tblCompanies:

SELECT tblIncoming.dtmDateReceived,
. . .,
tblCompanies.chrCompanyName

FROM (tblIncoming
LEFT JOIN tblCompanies
ON tblIncoming.chrCompany = tblCompanies.idsID)
LEFT JOIN tblOutgoing
ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]

WHERE . . .

The query could then be sorted as you want by using:
ORDER BY tblCompanies.chrCompanyName

And the report can be sorted by using the chrCompanyName in
Sorting and Grouping.