sorting missing null values (1 Viewer)

carlton123 · Jun 19, 2013

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 · Jun 19, 2013

Change your RecordSource to exclude Nulls..

Code:

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

carlton123 · Jun 19, 2013

pr2-eugin said:
Change your RecordSource to exclude Nulls..

Code:

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 · Jun 19, 2013

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

pr2-eugin · Jun 19, 2013

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 · Jun 19, 2013

pr2-eugin said:
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 · Jun 19, 2013

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

carlton123 · Jun 19, 2013

thats great works a treat just changed the 0 to date

cheers

SOS · Jun 19, 2013

carlton123 said:
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 · Jun 19, 2013

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

sorting missing null values (1 Viewer)

carlton123

Registered User.

pr2-eugin

Super Moderator

carlton123

Registered User.

pr2-eugin

Super Moderator

pr2-eugin

Super Moderator

SOS

Registered Lunatic

pr2-eugin

Super Moderator

carlton123

Registered User.

SOS

Registered Lunatic

carlton123

Registered User.

Similar threads

Users who are viewing this thread