Share via

Sorting subform rows with null values at end

Anonymous
2019-01-14T18:29:51+00:00

I have a subform that displays a subset of records based on a value entered in the main form.  The data is all the records for a specific language and I want to sort them by priority, but I want null values to be at the end of the list, not the beginning. I then need to be able to update the priority field in the subform.

So with the following data, I want to show it in priority sequence and then be able to update the priority and save. This would include updating priorities that have previously been entered and entering priorities that were previously null

Course          | Priority

Course A       | 1

Course B       | 2

Course C       | null

I've added this code to the Order By in the subform

       IIF([Translation Priority] IS NULL, 1, 0), [Translation Priority]

Which orders the rows correctly, but then I can't update the field. Am I trying to do the impossible? Any recommendations for a different design/method to try?

The database backend is running on an Azure SQL server.

Thanks for any help.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Doug Robbins - MVP - Office Apps and Services 323K Reputation points MVP Volunteer Moderator
2019-01-16T05:54:08+00:00

Try with the Recordset Type changed to Dynaset (Inconsistent Updates).

Was this answer helpful?

2 people found this answer helpful.
0 comments

Answer accepted by question author

Duane Hookom 26,825 Reputation points Volunteer Moderator
2019-01-14T18:34:15+00:00

Adding an expression to the Order By property of a form should not affect the ability to edit the values in fields. You can't edit controls with a control source beginning with "=". You don't need to display the value in the form in order to be able to use it for sorting.

I would probably use:

Sort By: Nz([Translation Priority],9999)

Can we assume [Translation Priority] is a numeric field?

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-01-16T20:35:49+00:00

    That fixed the update issue I was having. Thanks Doug

    Merrikay

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-01-14T19:40:48+00:00

    Thanks, Duane.

    Yes, [Translation Priority] is a numeric field. Your code works, but I can't update the field. I CAN update other fields in the form. When I take out the ORDER BY entry, the sequence is incorrect, but I can update the field.

    Here is the Property Sheet for the subform. See anything obvious?

    (Don't worry that "Priority" is missing the final "y"  That is correct.

    Thanks, 

    Merrikay

    Was this answer helpful?

    0 comments