Sorting will Null (Access 2K)

Topic

New Reply

I have a form which I would like to do an ascending sort on the field datPickUpDate. Is it possible to ignore the null fields and sort by the fields that have a value in it? I have attached a sample of my form.

Reply | Quote

Replies

Normally, Access will sort nulls before non-null values when sorting in ascending order, and after them when sorting in descending order. What exactly do you mean by “ignore the null fields”?

Reply | Quote

Hello Hans & Pat,

Thanks for your response. What I meant, Is this there a way to have the null fields appear at the end (after all the records that have a date value) when the sort is perform in ascending order?

Reply | Quote

Edit the Record Source of the form.
Remove the currently specified sort order.
Create a calculated column:

Dummy: IsNull([datPickUpDate])

You can clear the Show check box for this column, since it will only be used for sorting purposes.
Drag this column to the left of datPickUpDate.
Set the sort order for the dummy column to Descending, that for datPickUpDate to Ascending.
Close the query design and confirm that you want to save the changes.

Reply | Quote

brilliant!

Reply | Quote

Do you mean that you don’t want the records that contain a null value in the date field?

If so change the query behind the form to check for not null in the date field.

Reply | Quote