Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sorting will Null (Access 2K)

    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.
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting will Null (Access 2K)

    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"?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sorting will Null (Access 2K)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting will Null (Access 2K)

    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?
    Easy Access

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting will Null (Access 2K)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting will Null (Access 2K)

    brilliant!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •