Solved

Sort query with nulls at the end

Posted on 2014-07-21
6
211 Views
Last Modified: 2014-07-22
I have a query that sorts records ascending by date.  However, some of the date fields can be null, and I need these to come at the end of the list (currently they come at the start).

Here is my query:
SELECT tblClients.[Client ID], tblJobs.[Short description], Sum(tblJobVolLink.HoursWorked) AS VolHrsWorked, tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) LEFT JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID]
GROUP BY tblClients.[Client ID], tblJobs.[Short description], tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
ORDER BY tblJobs.[Date completed];

I cannot figure out how I can do this.

Regards

Richard
0
Comment
Question by:rltomalin
  • 3
  • 2
6 Comments
 
LVL 84
ID: 40208951
Have you tried a Descending sort:

SELECT tblClients.[Client ID], tblJobs.[Short description], Sum(tblJobVolLink.HoursWorked) AS VolHrsWorked, tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) LEFT JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID]
GROUP BY tblClients.[Client ID], tblJobs.[Short description], tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
ORDER BY tblJobs.[Date completed] DESC
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40208953
Create a field to handle the Sort Order, and assign nulls a large value, using NZ:


SELECT tblClients.[Client ID], tblJobs.[Short description], Sum(tblJobVolLink.HoursWorked) AS VolHrsWorked, tblJobs.[Actual supervisor hours], tblJobs.[Date completed], NZ(tblJobs.[Date completed]
, #1/1/2300#) AS DateCompletedOrderBy
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) LEFT JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID]
GROUP BY tblClients.[Client ID], tblJobs.[Short description], tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
ORDER BY  NZ(tblJobs.[Date completed]
, #1/1/2300#);

Open in new window

0
 

Author Comment

by:rltomalin
ID: 40209144
Scott - that would just reverse the sort order - the dates would be sorted descending - which I don't want.

mbizup
I tried your solution but it didn't seem to work.  Is there a problem with the date format?
I have attached the results with your code (+ the option omitting the #) but that doesn't work either.

I notice that when I show the sort field it is left aligned, whereas the actual date field is right aligned.  Is this a clue to the problem?

Regards
Date-sort-code.docx
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 40209259
Try this - using CDate on the order by field:

SELECT tblClients.[Client ID], tblJobs.[Short description], Sum(tblJobVolLink.HoursWorked) AS VolHrsWorked, tblJobs.[Actual supervisor hours], tblJobs.[Date completed], CDate(NZ(tblJobs.[Date completed]
, #1/1/2300#)) AS DateCompletedOrderBy
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) LEFT JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID]
GROUP BY tblClients.[Client ID], tblJobs.[Short description], tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
ORDER BY  CDate(NZ(tblJobs.[Date completed]
, #1/1/2300#));

Open in new window



Or alternatively this -- formatting a date string as yyyymmdd:


SELECT tblClients.[Client ID], tblJobs.[Short description], Sum(tblJobVolLink.HoursWorked) AS VolHrsWorked, tblJobs.[Actual supervisor hours], tblJobs.[Date completed], Format(NZ(tblJobs.[Date completed]
, #1/1/2300#),'yyyymmdd')  AS DateCompletedOrderBy
FROM (tblClients INNER JOIN tblJobs ON tblClients.[Client ID] = tblJobs.[Client ID]) LEFT JOIN tblJobVolLink ON tblJobs.[Job ID] = tblJobVolLink.[Job ID]
GROUP BY tblClients.[Client ID], tblJobs.[Short description], tblJobs.[Actual supervisor hours], tblJobs.[Date completed]
ORDER BY  Format(NZ(tblJobs.[Date completed]
, #1/1/2300#), 'yyyymmdd');

Open in new window

0
 

Author Comment

by:rltomalin
ID: 40209349
Hi mbizup
Home now.  Will try in the morning.
Thanks
Richard
0
 

Author Closing Comment

by:rltomalin
ID: 40211159
Hi mbizup

Excellent.  Does just what it says on the tin!!

Regards

Richard
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now