sorting missing null values

[carlton123]

Im trying to sort a form on a date. it only has dates put in once a confirmed date is known so when i sort all the blanks come to the top is there a way of sorting excluding null values

 

[pr2-eugin]

Change your RecordSource to exclude Nulls..

SELECT * FROM theTable WHERE theDateFieldName Is Not Null ORDER BY theDateFieldName;

 

[carlton123]

Change your RecordSource to exclude Nulls..

SELECT * FROM theTable WHERE theDateFieldName Is Not Null ORDER BY theDateFieldName;

cheers for that FAST reply would i still be able to sort by 3 things or would it miss all NULL records as i have a requested date column also which always has a date in

 

[pr2-eugin]

The code I gave will miss out the records with Null values in them unfortunately..

 

[pr2-eugin]

Just did a simple Google search.. I got to THIS.. this is in a Query.. Not sure if it would make it READ only.. Worth a try..

 

[SOS]

Just did a simple Google search.. I got to THIS.. this is in a Query.. Not sure if it would make it READ only.. Worth a try..

Yeah, I usually just create a sort field like that in the query itself. To do so in the QBE grid it would be something like this:

MySort:IIf([FieldNameHere] Is Null, 1, 0)

And then set to Ascending on the sort making sure it is the first sort in the sort order.

 

[pr2-eugin]

Thanks SOS for the explanation.. Now I get it..

 

[carlton123]

thats great works a treat just changed the 0 to date


cheers

 

[SOS]

thats great works a treat just changed the 0 to date

Not sure why you would do that. Because a 1 would equal 1/1/1900 if the Date field was used as the other. It keeps it cleaner if you just do like I said, and then have it as the first field in the ordering. You can then sort on the Date field with no worries. I'm not so sure you won't find yourself with a problem at some later time by doing the replacement that you stated.

 

[carlton123]

well i havent actually tried on database at work but one at home which actually wasnt a date field but i get what you mean now as in all the ones with dates in will sort first then sort on another field after. again many thanks