sort by ascending with the null values last

[daughtery]

I have the following query which I need to sort by "NextAppDate" ASC and then by rtnCode ASC but I need the records with a null value in the NextAppDate field to be returned after the records with values in the NextAppDate field. How can I adjustthe following query to do this?

SELECT  Catalog.RtnCode, 
        Catalog.TaxRtnID, 
        Catalog.Descript,
        CatalogTaxFormContactInfo.ContactName,
        CatalogTaxFormContactInfo.ContactNum,
        CatalogTaxFormContactInfo.ContactEmail,
        CatalogTaxFormContactInfo.ContactAdd1,
	CatalogTaxFormContactInfo.ContactAdd2,
	CatalogTaxFormContactInfo.ContactState,
	CatalogTaxFormContactInfo.ContactCounty,
	CatalogTaxFormContactInfo.ContactCity,
	CatalogTaxFormContactInfo.ContactZip,
	CatalogTaxFormContactInfo.FirstAppDate,
	CatalogTaxFormContactInfo.LastAppDate,
	CatalogTaxFormContactInfo.NextAppDate
FROM    Catalog
       Inner Join CatalogTaxFormContactInfo 
         on  CatalogTaxFormContactInfo.CatalogID = Catalog.CatalogID
	Order By NextAppDate ASC, RtnCode ASC

 

[gmmastros]

Try this...

[tt][blue]
Order By IsNull(NextAppDate, '30000101') ASC, RtnCode ASC
[/blue][/tt]

If the date is NULL, then a date of Jan 1, 3000 is used for the ordering.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[daughtery]

thanks