Re: Query sort not working after NULL column

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

OK, I take it back. It is NOT working when I remove SubTopic from the sort. I
could have sworn I saw it work but I tried it a couple more times and it does
not work, it mis-sorts exactly as it did before. This is driving me nuts.

"mscertified" wrote:

Some good ideas there.
I changed the data so the NULL column contains zero and the problem remains,
so the NULLs is not a factor. If I move the ORDER BY to qryRpt5YrProgress7,
that query sorts correctly, so the problem is related to this query not the
previous one.
I am using Access 2003 SP2
If I drop SubTopic from the ORDER BY, it sorts correctly!


"John Spencer" wrote:

Interesting. The data sorts correctly for me (Access 2003 SP2)

Is there a possibility that q.subTopic is not null, but is spaces or some
other not visible values.

What is in qryRpt5YrProgress7?

If you apply criteria to the query of
WHERE q.SubTopic is Null
so you still get the same records returned?

What happens if if you drop q.SubTopic from the ORDER BY clause? Do you
still see the same sorting problem?

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

"mscertified" <rupert@xxxxxxxxxxxxx> wrote in message
news:D42FB41B-9C7B-4E02-A96C-A8E6DB79B569@xxxxxxxxxxxxxxxx
Query:

SELECT Q.*, C.Desc AS ChapDesc, A.Desc AS ArtDesc
FROM (qryRpt5YrProgress7 AS Q LEFT JOIN tblChapters AS C ON
CInt(Q.Chapter)
= CInt(C.Chapter)) LEFT JOIN tblArticles AS A ON (Q.Title = A.Title) AND
(Q.Chapter = A.Chapter) AND (Q.Article = A.Article)
ORDER BY Q.Title, Q.Chapter, Q.Article, Q.Topic, Q.SubTopic, Q.PkgID;

Sample output:
R9,22,2,17,Null,1867
R9,22,2,17,Null,1852
R9,22,2,17,Null,1852
R9,22,2,17,Null,1867
....
R9,22,2,17,Null,1885
R9,22,2,17,Null,1852

As you can see, last column is unsorted



"Jerry Whittle" wrote:

Does the data in column E have numbers in it that look something like
street
addressses: 123 Main St? Numbers in text strings can seem to be sorted
incorrectly, but actually are not.

Provide some sample data where the sort isn't working right.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"mscertified" wrote:

I have a query that has an order by e.g.
ORDER BY A,B,C,D,E
For this particular subset of data, columns A, B and C are all the
same,
column D is NULL in each row. However, column E has different values.
The
sort is not sorting by column E. The only thing I can think of is the
NULLs
in column D are throwing it off. Is this correct or should it sort
column E
corectly even if column D is NULL.

Hope someone can shed light on this?



.

Relevant Pages

  • Re: Query doesnt always sort
    ... using a query or in Access imposing a sort order on the datasheet view. ... primary key in the table, then Access will show the records in primary key ... Access MVP 2002-2005, 2007 ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... My table doesn't have a primary key. ... How do I impose a sort on the datasheet view? ... using a query or in Access imposing a sort order on the datasheet view. ... Access MVP 2002-2005, 2007 ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... Dim OutputTable As DAO.Recordset ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... I just realized that you are using the query to do a make table. ... Make table queries can more or less ignore the sort order when they are ... Access MVP 2002-2005, 2007 ... reports more or less ignore any sorting done by the query. ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... You sort in a query, ... You can do a compound sort in a query. ... "Tom Ellison" wrote: ... resulting table only has one record for each value of "CAMPNO". ...
    (microsoft.public.access.queries)