Sorting on blank field

[radiorog]

I'm new to this forum, so please excuse me if this is an elementary question.

I have a date field in a database that sometimes contains data, but sometimes doesn't, depending on the individual record. I index on this date field for one report (in ascending order), and the records with empty date fields get placed ahead of the records with dates in the date field. (Is this making sense?) Anyway, I'd prefer to have the records with empty date fields get placed _after_ the records with data in the date field (still with the index in ascending order), but can't quite figure out how to do this.

Anyone have a magic bullet to pull this off?

Thanks.

Roger Rittner
radiorog@earthlink.net

 

[DSummZZZ]

There are a couple ways to go about it. One is to create a user defined function to skate along. Since I don't think it's ever a good idea to rely on a UDF for an index key I would prefer to produce the report twice or use a query to produce the report:

UDF
*............ proc file ...................
Function MTDateSort
IF EMPTY(DateField)
RETURN 99999999
ELSE
RETURN DTOC(DateField)
ENDIF
*..........................................

Then:
INDEX ON MTDateSort() TAG MTDate

Or report twice:

REPORT FORM whatever FOR .NOT. EMPTY(DateField)
REPORT FORM whatever FOR EMPTY(DateField)

Or a query:
SELECT * FROM somefile WHERE .NOT. EMPTY(DateField) ;
INTO TABLE Sometable
SELECT * FROM somefile WHERE EMPTY(DateField) ;
INTO TABLE anothertable
USE IN anothertable
SELECT Sometable
APPEND FROM anothertable

REPORT FORM whatever.....

Dave S.

 

[JRB-Bldr]

Or create an index specific to the report output such as:

INDEX ON IIF(!EMPTY(DateField),DTOS(DateField),"99999999&quot ;
TAG RptDate

Then,
SET ORDER TO RptDate
REPORT FORM YourReport ....

I find that DTOS() is more reliable at yielding ascending date order than DTOC().

Good Luck,
jrbbldr
jrbbldr@yahoo.com

 

[radiorog]

That did it! Perfect! Just what I was looking for.

Thanks so much!

Roger Rittner