Text Field Sort Order

[Karl Burrows]

I posted a week or so ago and thought I had an answer, but now it doesn't
want to sort correctly.

The field name is LotNumber. I need to convert it to a text field to allow
an additional text character. The value may be something like 1, 2, 2a, 2b,
2c, etc. I tried using VAL([LotNumber]) and sorting by this value and it
still wants to sort 1, 10, 100, 101, 11, 12, 2, 20, 21, etc. What am I
doing wrong to get it to sort correctly?

Thanks!

 

[Dirk Goldgar]

I posted a week or so ago and thought I had an answer, but now it
doesn't want to sort correctly.

The field name is LotNumber. I need to convert it to a text field to
allow an additional text character. The value may be something like
1, 2, 2a, 2b, 2c, etc. I tried using VAL([LotNumber]) and sorting by
this value and it still wants to sort 1, 10, 100, 101, 11, 12, 2, 20,
21, etc. What am I doing wrong to get it to sort correctly?

If you were truly sorting by Val([LotNumber]), you wouldn't get the
order you report. So something's wrong. How and where is this sort
being applied? In a query? If so, print the SQL of the query.

 

[tina]

i tried to duplicate your results in a query, but instead got a correct sort
using the Val() function. are you sorting the data in a query? if so, pls
post the SQL.
if you're sorting it the data someplace else, where and how?

 

[Karl Burrows]

I think I figured it out. It was working in the query and not the report,
so I changed the grouping in the report to sort the VAL sort and not the Lot
Number and that seemed to work okay.

Now, to take this further, if we were to change the fields to say 1a, 1aa,
2a, 2ac, will this still sort correctly? I added the original field sort to
the right of this one to resort again. Is that correct and if so, do I need
to change anything in the report to keep that order as well?

Thanks!

i tried to duplicate your results in a query, but instead got a correct sort
using the Val() function. are you sorting the data in a query? if so, pls
post the SQL.
if you're sorting it the data someplace else, where and how?

 

[Wayne Morgan]

If you use the Sorting and Grouping dialog in a report, any sorting already
done in the query feeding the report is ignored.

You will need 2 calculated fields solely for sorting purposes. The first one
is the one you already have. The second one would be

Mid([FieldName], Len(CStr(Val([FieldName]))) + 1)

 

[Karl Burrows]

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

Thanks!

If you use the Sorting and Grouping dialog in a report, any sorting already
done in the query feeding the report is ignored.

You will need 2 calculated fields solely for sorting purposes. The first one
is the one you already have. The second one would be

Mid([FieldName], Len(CStr(Val([FieldName]))) + 1)

 

[Wayne Morgan]

If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

 

[Karl Burrows]

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

 

[tina]

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[Wayne Morgan]

In a query, the fields are sorted in the order they are listed in the design
grid, left to right. In the Sorting and Grouping dialog, they are sorted in
the order they are listed, top to bottom. So, if you want to sort by the
numeric value first, that field needs to be on the left or top.

--
Wayne Morgan
MS Access MVP

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the
report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
message
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you
change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[Karl Burrows]

Still having issues sorting on some reports. I do not use the field values
created in the query within the report (or should I?), so the numbers still
sort as text, even if I add grouping and sorting by LotSortAlpha. It works
on some reports and not on others. Very strange. Could it have anything to
do with other sorting criteria in columns before this sort?

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[Karl Burrows]

Also, does this need to be an expression or just Group By?

Thanks!

Still having issues sorting on some reports. I do not use the field values
created in the query within the report (or should I?), so the numbers still
sort as text, even if I add grouping and sorting by LotSortAlpha. It works
on some reports and not on others. Very strange. Could it have anything to
do with other sorting criteria in columns before this sort?

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[tina]

I do not use the field values created in the query within the report

(or should I?)

if you mean you don't use those calculcated query fields to display data in
the report, then no, that shouldn't make a difference to the
sorting/grouping.

Also, does this need to be an expression or just Group By?

you lost me entirely on that one.
let's back up a step. you already created the two calculated fields in your
query. set a sort on those two fields in the query, then open the query in
datasheet view. does it sort as intended?

if so, close the query without saving the Sort changes. go back to your
report, and make sure that you've set the Sort order in the report as
LotNumberSort
LotSortAlpha

you shouldn't need to add a grouping level just to get a correct sort.

Could it have anything to do with other sorting criteria in columns
before this sort?

yes. in a query, columns are sorted left to right, as Wayne said earlier,
and top to bottom in a report's Sorting And Grouping box. so if you have
other fields that are sorted ahead of the two we've been working with, then
that will of course affect the final sort order imposed on the recordset.

hth

Also, does this need to be an expression or just Group By?

Thanks!

Still having issues sorting on some reports. I do not use the field values
created in the query within the report (or should I?), so the numbers still
sort as text, even if I add grouping and sorting by LotSortAlpha. It works
on some reports and not on others. Very strange. Could it have anything to
do with other sorting criteria in columns before this sort?

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[Karl Burrows]

If I set the sort order in the query, then it opens fine.

The light bulb just went off! I was creating a sort in the report, but just
confusing it with grouping since I don't create a header or footer, etc.
Yes, if I create a sort for the LotNumberSort first and then the
LotSortAlpha, it works fine. I kept thinking I had to sort it another way
instead of using the Sorting and Grouping window, but since all I am doing
is sorting this works. I also wasn't creating both sorts, just the alpha
thinking the query did the actual sorting, but since I am not using those
fields, I have to create the sort in the report to get it in the proper
order. Am I right now?

Sorry I am making this so hard! Thanks!

I do not use the field values created in the query within the report
(or should I?)

if you mean you don't use those calculcated query fields to display data in
the report, then no, that shouldn't make a difference to the
sorting/grouping.

Also, does this need to be an expression or just Group By?

you lost me entirely on that one.
let's back up a step. you already created the two calculated fields in your
query. set a sort on those two fields in the query, then open the query in
datasheet view. does it sort as intended?

if so, close the query without saving the Sort changes. go back to your
report, and make sure that you've set the Sort order in the report as
LotNumberSort
LotSortAlpha

you shouldn't need to add a grouping level just to get a correct sort.

Could it have anything to do with other sorting criteria in columns
before this sort?

yes. in a query, columns are sorted left to right, as Wayne said earlier,
and top to bottom in a report's Sorting And Grouping box. so if you have
other fields that are sorted ahead of the two we've been working with, then
that will of course affect the final sort order imposed on the recordset.

hth

Also, does this need to be an expression or just Group By?

Thanks!

Still having issues sorting on some reports. I do not use the field values
created in the query within the report (or should I?), so the numbers still
sort as text, even if I add grouping and sorting by LotSortAlpha. It works
on some reports and not on others. Very strange. Could it have anything to
do with other sorting criteria in columns before this sort?

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP

That makes sense, since the grouping was sorting by Lot Number, so it was
over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[tina]

sounds like you got it working now, good job! and you're welcome.

If I set the sort order in the query, then it opens fine.

The light bulb just went off! I was creating a sort in the report, but just
confusing it with grouping since I don't create a header or footer, etc.
Yes, if I create a sort for the LotNumberSort first and then the
LotSortAlpha, it works fine. I kept thinking I had to sort it another way
instead of using the Sorting and Grouping window, but since all I am doing
is sorting this works. I also wasn't creating both sorts, just the alpha
thinking the query did the actual sorting, but since I am not using those
fields, I have to create the sort in the report to get it in the proper
order. Am I right now?

Sorry I am making this so hard! Thanks!

I do not use the field values created in the query within the report
(or should I?)

if you mean you don't use those calculcated query fields to display data in
the report, then no, that shouldn't make a difference to the
sorting/grouping.

Also, does this need to be an expression or just Group By?

you lost me entirely on that one.
let's back up a step. you already created the two calculated fields in your
query. set a sort on those two fields in the query, then open the query in
datasheet view. does it sort as intended?

if so, close the query without saving the Sort changes. go back to your
report, and make sure that you've set the Sort order in the report as
LotNumberSort
LotSortAlpha

you shouldn't need to add a grouping level just to get a correct sort.

Could it have anything to do with other sorting criteria in columns
before this sort?

yes. in a query, columns are sorted left to right, as Wayne said earlier,
and top to bottom in a report's Sorting And Grouping box. so if you have
other fields that are sorted ahead of the two we've been working with, then
that will of course affect the final sort order imposed on the recordset.

hth

Also, does this need to be an expression or just Group By?

Thanks!

Still having issues sorting on some reports. I do not use the field values
created in the query within the report (or should I?), so the numbers still
sort as text, even if I add grouping and sorting by LotSortAlpha. It works
on some reports and not on others. Very strange. Could it have

anything

to
do with other sorting criteria in columns before this sort?

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP


That makes sense, since the grouping was sorting by Lot Number, so

it
was

over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?

 

[Karl Burrows]

Whew! Thanks for your and Wayne's patience!

sounds like you got it working now, good job! and you're welcome.

If I set the sort order in the query, then it opens fine.

The light bulb just went off! I was creating a sort in the report, but just
confusing it with grouping since I don't create a header or footer, etc.
Yes, if I create a sort for the LotNumberSort first and then the
LotSortAlpha, it works fine. I kept thinking I had to sort it another way
instead of using the Sorting and Grouping window, but since all I am doing
is sorting this works. I also wasn't creating both sorts, just the alpha
thinking the query did the actual sorting, but since I am not using those
fields, I have to create the sort in the report to get it in the proper
order. Am I right now?

Sorry I am making this so hard! Thanks!

I do not use the field values created in the query within the report
(or should I?)

if you mean you don't use those calculcated query fields to display data in
the report, then no, that shouldn't make a difference to the
sorting/grouping.

Also, does this need to be an expression or just Group By?

you lost me entirely on that one.
let's back up a step. you already created the two calculated fields in your
query. set a sort on those two fields in the query, then open the query in
datasheet view. does it sort as intended?

if so, close the query without saving the Sort changes. go back to your
report, and make sure that you've set the Sort order in the report as
LotNumberSort
LotSortAlpha

you shouldn't need to add a grouping level just to get a correct sort.

Could it have anything to do with other sorting criteria in columns
before this sort?

yes. in a query, columns are sorted left to right, as Wayne said earlier,
and top to bottom in a report's Sorting And Grouping box. so if you have
other fields that are sorted ahead of the two we've been working with, then
that will of course affect the final sort order imposed on the recordset.

hth

Also, does this need to be an expression or just Group By?

Thanks!

Still having issues sorting on some reports. I do not use the field values
created in the query within the report (or should I?), so the numbers still
sort as text, even if I add grouping and sorting by LotSortAlpha. It works
on some reports and not on others. Very strange. Could it have

anything

to
do with other sorting criteria in columns before this sort?

try the following in your query:

add
LotNumberSort: Val([LotNumber])
then add
LotSortAlpha: [LotNumber]
to the RIGHT of LotNumberSort. sort both, ascending or descending as needed.
note: if you're doing any sorting/grouping in a report bound to this query,
don't bother to set a sort order in the query itself. just do the sort in
the report, listing the fields in order: LotNumberSort, then LotSortAlpha.

note: if there's any possibility that a record may have a Null value in the
LotNumber field, change the expression to
LotNumberSort: Val(Nz([LotNumber], 0))

hth

Still getting some issues with the sort:

Setup query with table field value LotClosings, no sort

Added column to the right called LotNumberSort: Val([LotNumber]) as
Expression

Added a second column to the left of LotNumberSort called SortLotAlpha:
Mid([LotNumber],Len(CStr(Val([LotNumber])))+1).

I also removed the grouping and sorting option for Lot Number in the report
and the sort is still backwards or random if there are text values in the
field value.

Did I do something wrong here?

Thanks!

This still has issues sorting
If you use the equation you currently have for the numeric sort then use
this one for the alpha sort, it will first sort by the number then by the
alpha characters after the number. "a" will come before "aa". It will work
with any number of characters or digits in front of the characters. The
equation in the Len statement tells the Mid() function to start after the
last numeric character and since a number of characters isn't specified
(that would be the 3rd parameter, but it has been left blank) then it will
continue to the end of the string. What this won't work for is if you change
the pattern of where the numbers and letters are, such as 1a2b.

--
Wayne Morgan
MS Access MVP


That makes sense, since the grouping was sorting by Lot Number, so

it
was

over riding the sort from the query.

Does this work for 1aaa, 1abc as well or just two text characters?