Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sort query with nulls at the end

Posted on 2014-07-21
6
Medium Priority
?
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 85
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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