Criteria Messing Up Sort Order

[Guest]

Hello all! This is my first post to the Access forum. I did some searching on
this, but didn't find an answer...

I have a query with about 10 fields on it. I sort ascending on field1 and
then ascending on field2, and everything works fine. Query results look great.

Then, the only thing I do is add a criteria, "<100", on a third field and
the sort order falls apart.

Any ideas? It's bizarre to me...

 

[Guest]

That does sound strange. Are you sorting on numerical characters that are in
a text field by chance?

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Maybe show some sample data on the proper sort
and what is going wrong.

 

[Guest]

No, the sort feilds are Text50.

That does sound strange. Are you sorting on numerical characters that are in
a text field by chance?

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Maybe show some sample data on the proper sort
and what is going wrong.

 

[John W. Vinson]

Hello all! This is my first post to the Access forum. I did some searching on
this, but didn't find an answer...

I have a query with about 10 fields on it. I sort ascending on field1 and
then ascending on field2, and everything works fine. Query results look great.

Then, the only thing I do is add a criteria, "<100", on a third field and
the sort order falls apart.

Any ideas? It's bizarre to me...

Note that a criterion of

"<100"

will find records where the Text field contains the literal four-character
string "<100". If you are treating these text fields as numbers, and you want
this criterion to retrieve records with the field equal to 90 and exclude
records with the field equal to 1000, you'll be disappointed!

If you'll be doing numeric sorts and/or numeric value criteria, you need to
change the datatype of the fields to some flavor of Number (the default Long
Integer if you don't need decimal places, Currency or Double if you do); or
use a more complex query using the Val() function to convert the text string
to a number for comparison or sorting.

John W. Vinson [MVP]

 

[John Spencer]

Where are you seeing the Sort Order change? In the query or in a report
based on the query?

Report sort order has no defined relationship to the query sort order.
Sometimes it matches, sometimes it doesn't.

You specify a report's sort order using the View: Sorting and Grouping menu.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

No, the record population is correct, it's just ignoring the ORDER BY clause.

 

[Guest]

The sort order is being thrown off in the actual query results. It's weird. I
simply add a criteria on a field and the two sorts cease to function. They're
still in the SQL statement ORDER BY, but they are clearly being ignored.

 

[David Cox]

I have not seen the SQL.

Longshot - Have you got something like ([Field]<100) which will return true
(-1) or false (0) ?

 

[John W. Vinson]

No, the record population is correct, it's just ignoring the ORDER BY clause.

As Jerry and John have requested - please post the SQL.

We can't fix an error when we cannot see the query that's causing the error.

John W. Vinson [MVP]

 

[Guest]

All,

Here's the SQL that works:
SELECT Assignment_Table.Resource_Group,
Assignment_Table.Resource_Name,
Assignment_Table.Percent_Work_Complete,
Assignment_Table.Task_ID,
Assignment_Table.Task_Name,
Assignment_Table.Task_Summary_Name,
Assignment_Table.Task_Unique_ID,
Assignment_Table.Start_Date,
Assignment_Table.Finish_Date,
Task_Table.Recurring
FROM Assignment_Table INNER JOIN Task_Table ON
Assignment_Table.Task_Unique_ID = Task_Table.Unique_ID
WHERE (((Task_Table.Recurring)=0))
ORDER BY Assignment_Table.Resource_Group, Assignment_Table.Resource_Name;

And here's the SQL that doesn't sort properly (appears to be ignoring the
ORDER BY clause):

SELECT Assignment_Table.Resource_Group,
Assignment_Table.Resource_Name,
Assignment_Table.Percent_Work_Complete,
Assignment_Table.Task_ID,
Assignment_Table.Task_Name,
Assignment_Table.Task_Summary_Name,
Assignment_Table.Task_Unique_ID,
Assignment_Table.Start_Date,
Assignment_Table.Finish_Date,
Task_Table.Recurring
FROM Assignment_Table INNER JOIN Task_Table ON
Assignment_Table.Task_Unique_ID = Task_Table.Unique_ID
WHERE (((Assignment_Table.Percent_Work_Complete)<100) AND
((Task_Table.Recurring)=0))
ORDER BY Assignment_Table.Resource_Group, Assignment_Table.Resource_Name;



:

[snip]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Maybe show some sample data on the proper sort
and what is going wrong.

[snip

 

[John W. Vinson]

And here's the SQL that doesn't sort properly (appears to be ignoring the
ORDER BY clause):

SELECT Assignment_Table.Resource_Group,
Assignment_Table.Resource_Name,
Assignment_Table.Percent_Work_Complete,
Assignment_Table.Task_ID,
Assignment_Table.Task_Name,
Assignment_Table.Task_Summary_Name,
Assignment_Table.Task_Unique_ID,
Assignment_Table.Start_Date,
Assignment_Table.Finish_Date,
Task_Table.Recurring
FROM Assignment_Table INNER JOIN Task_Table ON
Assignment_Table.Task_Unique_ID = Task_Table.Unique_ID
WHERE (((Assignment_Table.Percent_Work_Complete)<100) AND
((Task_Table.Recurring)=0))
ORDER BY Assignment_Table.Resource_Group, Assignment_Table.Resource_Name;

That is truly bizarre. I don't see anything that would cause the sorting to
fail.

A couple of questions:

Do you have Name Autocorrect (familiarly known among the experienced as Name
Autocorrupt) turned off? If not, turn it off; compact and repair; and rebuild
the query from scratch.

Might there be something wrong with the indexes on the table? Try dropping any
indexes on Percent_Work_Complete, Recurring, Resource_Group and Resource_Name;
compact the database; and recreate these indexes.

John W. Vinson [MVP]

 

[David Cox]

I always have trouble matching brackets, and I cannot match them in this
SQL. It could be just me .....

 

[John W. Vinson]

I always have trouble matching brackets, and I cannot match them in this
SQL. It could be just me .....

I think they match (and a mismatch would cause an error message, not a bad
sort).

I learned ages ago to count parens by adding 1 for each left, and subtracting
1 for each right. If you get to the end of the string with anything other than
0 you're mismatched.

John W. Vinson [MVP]

 

[David Cox]

Yes it would, I suspected that we were not getting the true picture. I will
try counting again.

David F. Cox