Sort help needed

[JudyHNM]

I am using Access 2016. I have a large list of numbers and text that I want to be sorted within the form. In the table, the field is defined as text as there are some text values that have to be entered. When I use a query to sort the records, I end up with 327, 3325, 3326, 333 when I want the sort to appear as 327, 333, 3325, 3326. How I can I get the data to sort this way?

Thanks, Judy

 

[stumac]

How should values of null or text values be sorted - at the beginning or end?

I would add in a calculated field to your query, sort on this first and then by your field... something like:

SortOrder: IIf(IsNumeric(Nz([MyField],0)),Val(Nz([MyField],999999)),999998)

If your field value can be higher than 999998 then adjust as appropriate. This way the numbers would be sorted first, then the text values and then the null values.

 

[JudyHNM]

It doesn't matter to me where nulls end up as long as it's consistent, I can deal with it. My "text numbers" are 3 and 4 digits in length so this is more than adequate. I will give it a try -- thanks so much for your response.

Judy