Sorting two columns by date 1

[CJSilver]

I am having a problem sorting my query(though the sort could be done in the form also, if it would be easier).

I have two date columns, one, schedule date, has dates in every row, the other is build date, this date is filled in using a form. But not all rows are filled in, most are left blank. I want my sort to show the build dates first, then for all the rows without build dates, sorted by schedule date. I thought that just sorting as:

ORDER BY scheduleandform.[Build Date], scheduleandform.SCHED_DATE;

would accomplish what I needed, but it does not. When I sort like this it gives me all the build date blank rows first.

C. Johnson

http://www.SilveradoCable.com

 

[RoyVidar]

If I understand correctly, try:

[tt]ORDER BY iif(isnull(scheduleandform.[Build Date]), scheduleandform.SCHED_DATE,scheduleandform.[Build Date]);[/tt]

Roy-Vidar

 

[CJSilver]

Hello Roy,

Thank you for replying.

The sort still does not come out correctly. When I try what you suggested, I get the data sorted by sched_date, with the build dates showing up where the same date would show up in the sched_date column. It's putting the list in date order, if there is a build date it uses that, if not it uses the sched_date.
Example:

Build Date Sched_date Job#
03/21/05 100
03/21/05 115
03/24/05 03/18/05 113
03/28/05 04/10/05 122
04/01/05 118

This is how I want it:

Build Date Sched_Date Job#
03/24/05 03/18/05 113
03/28/05 04/10/05 122
03/21/05 100
03/21/05 115
04/01/05 118

I want to see the Build Dates on top in order, then the rest is sorted by Sched_date.

C. Johnson

http://www.SilveradoCable.com

 

[RoyVidar]

Probably don't understand a thing, but have another go;-)

[tt]ORDER BY iif(isnull(scheduleandform.[Build Date]), #1/1/2100#,scheduleandform.[Build Date]), scheduleandform.SCHED_DATE;[/tt]

i e - do the "dirty" trick of replacing Null values in the first column with a "high" date, to have them sort below non Nulls;-)

Roy-Vidar

 

[CJSilver]

Good Job Roy!

Thank you, that works!

C. Johnson

http://www.SilveradoCable.com