"Sort" a combination of letters and numbers e.g. 20, 21a,200, 105c

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The numbers represent buildings and are stored as text. The Sort Ascending
and Decending option sorts the numbers only first and then the
numbers/letters. I want the list to end up as 20, 21a, 105c, 200. This is
a problem in Excess too. Help and thanks.
 
Crazy said:
The numbers represent buildings and are stored as text. The Sort Ascending
and Decending option sorts the numbers only first and then the
numbers/letters. I want the list to end up as 20, 21a, 105c, 200.
Click to expand...


You can sort on expressions, not just fields.

Try setting two levels of sort in Sorting and Grouping to:
=Val(numfield)
=numfield
 
Thanks for replying Marshall. I don't think this helps me, because I don't
know how to use code. I only have one piece of code in my database so far,
and that was given to me on this site.

If you wouldn't mind eleborating I would be very grateful.

You may be able to help further. I am wanting to sort in a report mainly,
but possibly also in a form and there are two possible sort criteria. I may
want to sort by this field or by another. I was intending to create two
reports sorted in those two ways and have a prompt ask which report to show.
Is there a better way?

Crazy Lady
 
But, Lady, that's not "code", it's just a trivial expression
in one line of the Sorting and Grouping window. The second
one is just a field name selected from the field/expression
drop down list. It sounds like you may be unfamiliar with
using Sorting and Grouping (View menu). If so, take a
little time out from making extra work for yourself and get
familiar with this powerful, but easy to use feature of
Access reports. Then give my earlier suggestion a try.

Note that, unlike forms, anything beyond a trivial report
**must** use Sorting and Grouping to specify it's sorting.
Changing this according to user input requires a small
amount of real VBA code. If your up to it, post back with
specific details for a specific report and I'll provide some
code for you to try.

As to your other question, you can use a slightly more
complex expression (not code) to cause your form's record
source **query** to be sorted in either of two ways. First,
some form's control (text box, check box, combo box, etc)
should be used to allow the users to specify which sort they
want. Since you didn't provide much in the way of clues to
your requirements, I can only provide the general idea.
Add a calculated field to the query:
SortVal:IIf(Forms!someform.somecontrol = val1,fieldA,fieldB)
 
Hi Marsh,

Sorry it's been a while but I have real work to do and I have to fit my
database in as I can.

Right. The sorting for the report works beautifully, however, any VBA code
I can get for reference and helping me to learn is most welcome. To that
end: The Report is called "Register" and the columns I want to sort on are
"Area" (which contains alphanumeric values) and Tag (numeric values).
Sometimes I will want to sort by a) area and then b) Tag, but sometimes I
will just want to sort by Tag.

Can I come back to you later when I have tried your second answer in several
ways? I haven't got it to work yet, but that my be due to the way the query
is set up.

Cheers

Crazy Lady

Marshall Barton said:
But, Lady, that's not "code", it's just a trivial expression
in one line of the Sorting and Grouping window. The second
one is just a field name selected from the field/expression
drop down list. It sounds like you may be unfamiliar with
using Sorting and Grouping (View menu). If so, take a
little time out from making extra work for yourself and get
familiar with this powerful, but easy to use feature of
Access reports. Then give my earlier suggestion a try.

Note that, unlike forms, anything beyond a trivial report
**must** use Sorting and Grouping to specify it's sorting.
Changing this according to user input requires a small
amount of real VBA code. If your up to it, post back with
specific details for a specific report and I'll provide some
code for you to try.

As to your other question, you can use a slightly more
complex expression (not code) to cause your form's record
source **query** to be sorted in either of two ways. First,
some form's control (text box, check box, combo box, etc)
should be used to allow the users to specify which sort they
want. Since you didn't provide much in the way of clues to
your requirements, I can only provide the general idea.
Add a calculated field to the query:
SortVal:IIf(Forms!someform.somecontrol = val1,fieldA,fieldB)
--
Marsh
MVP [MS Access]


Crazy said:
Thanks for replying Marshall. I don't think this helps me, because I don't
know how to use code. I only have one piece of code in my database so far,
and that was given to me on this site.

If you wouldn't mind eleborating I would be very grateful.

You may be able to help further. I am wanting to sort in a report mainly,
but possibly also in a form and there are two possible sort criteria. I may
want to sort by this field or by another. I was intending to create two
reports sorted in those two ways and have a prompt ask which report to show.
Is there a better way?
Click to expand...
Click to expand...
 
Hi Again,

No matter what I do to the underlying query of my form I cannot get the
SortVal to work. It will not sort by area including both the alpha and
numeric values and it's not sorting the Tag exactly either. Strictly
speaking the Tag is alphanumeric, but with an input mask of GL/ followed by
numbers as in GL/0001 or GL/1561. The Form Sort will sort the Tag field
correctly but not the Area field. The SortVal calculated field is not doing
either.

I know I've got this sorted out in a report, but sometimes I will just need
to view the data (with more fields than the report contains). By the way,
have I mentioned that my form is in Data sheet view?

Help!

Crazy Lady

Marshall Barton said:
But, Lady, that's not "code", it's just a trivial expression
in one line of the Sorting and Grouping window. The second
one is just a field name selected from the field/expression
drop down list. It sounds like you may be unfamiliar with
using Sorting and Grouping (View menu). If so, take a
little time out from making extra work for yourself and get
familiar with this powerful, but easy to use feature of
Access reports. Then give my earlier suggestion a try.

Note that, unlike forms, anything beyond a trivial report
**must** use Sorting and Grouping to specify it's sorting.
Changing this according to user input requires a small
amount of real VBA code. If your up to it, post back with
specific details for a specific report and I'll provide some
code for you to try.

As to your other question, you can use a slightly more
complex expression (not code) to cause your form's record
source **query** to be sorted in either of two ways. First,
some form's control (text box, check box, combo box, etc)
should be used to allow the users to specify which sort they
want. Since you didn't provide much in the way of clues to
your requirements, I can only provide the general idea.
Add a calculated field to the query:
SortVal:IIf(Forms!someform.somecontrol = val1,fieldA,fieldB)
--
Marsh
MVP [MS Access]


Crazy said:
Thanks for replying Marshall. I don't think this helps me, because I don't
know how to use code. I only have one piece of code in my database so far,
and that was given to me on this site.

If you wouldn't mind eleborating I would be very grateful.

You may be able to help further. I am wanting to sort in a report mainly,
but possibly also in a form and there are two possible sort criteria. I may
want to sort by this field or by another. I was intending to create two
reports sorted in those two ways and have a prompt ask which report to show.
Is there a better way?
Click to expand...
Click to expand...
 
Just got back from a trip to Microsoft on the west coast.

Sorting a form is differnt from sorting a report. In a form
you can use either the form's Filter property or the form's
record source query.

I don't have enough details to make much in the way of a
suggestion. You said it doesn't work, but I don't know what
you tried nor the result that was produced.
 
Hi Marshall, Welcome back.

You sorted me out well with the report problem (no pun intended), however,
that puts information in a format for me to send to others.

I want possibly the "live" items in my database to all be visible in form
datasheet view. The database basically consists of structures located in
various places and with different ID nos. I can easily get the form sort
function to sort the entire list by Client or by order number, but because
the locations "area" and "Tag No." (ID no.) are in an alphanumeric format,
the sort ascending/descending function is not working. I did try the query
field sort that you gave me, but I need the sort to be carried out at it is
in the report. The sort by in the query field only allows
ascending/descending or not sorted.

I hope I'm saying this OK. The "area" field, as before, comprises of 20,
21a, 67s, 88 105d etc but the sort ignores the alphas and sorts the numbers
only first and then numbers with alphas.

Similarly, the "Tag No." field has an alpha input mask of GL/ followed by 4
digits e.g. GL/0001, GL/1576 etc.

Sometimes it would be helpful for me to see a list of items by area, but
sometimes by Tag No. (ID no.).

It would be doubly helpful if I could sort by "Area" then by " " , or by
"Tag No." then by " ". But if that's too much to ask I can live with it.

I suspect I do need to run a code but I wouldn't know where to start with
that.

Hope you can help.

Crazy Lady


Marshall Barton said:
Just got back from a trip to Microsoft on the west coast.

Sorting a form is differnt from sorting a report. In a form
you can use either the form's Filter property or the form's
record source query.

I don't have enough details to make much in the way of a
suggestion. You said it doesn't work, but I don't know what
you tried nor the result that was produced.
--
Marsh
MVP [MS Access]


Crazy said:
No matter what I do to the underlying query of my form I cannot get the
SortVal to work. It will not sort by area including both the alpha and
numeric values and it's not sorting the Tag exactly either. Strictly
speaking the Tag is alphanumeric, but with an input mask of GL/ followed by
numbers as in GL/0001 or GL/1561. The Form Sort will sort the Tag field
correctly but not the Area field. The SortVal calculated field is not doing
either.

I know I've got this sorted out in a report, but sometimes I will just need
to view the data (with more fields than the report contains). By the way,
have I mentioned that my form is in Data sheet view?
Click to expand...
Click to expand...
 
It might help you to understand that text values (which may
contain any character, including only digits), are sorted in
"dictionary" order. This means that this list is sorted
correctly:
1a
1b
17
188x
2
234432gg
34qwe

When I suggested using Val(area) before, I was unaware of
how you wanted to deal with the trailing characters, so it
only sorted by the initial number part or the text value.
Now you're saying something else is happening, but I need to
see the kind of values you're working with and what order
they are supposed to be sorted.

The same kind of info is needed for the tag. Your example
only shows tags starting with GL/, but you said that was a
mask. A mask may or may not store those characters in the
field, so that detail is very important. If the GL/ is
stored and that's the only prefix allowed, then this field
can be sorted by using the expression Val(Mid([Tag No], 4))

Be sure to check Help for explanations of any of these
functions that you are not familiar with.
--
Marsh
MVP [MS Access]


Crazy said:
You sorted me out well with the report problem (no pun intended), however,
that puts information in a format for me to send to others.

I want possibly the "live" items in my database to all be visible in form
datasheet view. The database basically consists of structures located in
various places and with different ID nos. I can easily get the form sort
function to sort the entire list by Client or by order number, but because
the locations "area" and "Tag No." (ID no.) are in an alphanumeric format,
the sort ascending/descending function is not working. I did try the query
field sort that you gave me, but I need the sort to be carried out at it is
in the report. The sort by in the query field only allows
ascending/descending or not sorted.

I hope I'm saying this OK. The "area" field, as before, comprises of 20,
21a, 67s, 88 105d etc but the sort ignores the alphas and sorts the numbers
only first and then numbers with alphas.

Similarly, the "Tag No." field has an alpha input mask of GL/ followed by 4
digits e.g. GL/0001, GL/1576 etc.

Sometimes it would be helpful for me to see a list of items by area, but
sometimes by Tag No. (ID no.).

It would be doubly helpful if I could sort by "Area" then by " " , or by
"Tag No." then by " ". But if that's too much to ask I can live with it.

I suspect I do need to run a code but I wouldn't know where to start with
that.

Hope you can help.

Crazy Lady


Marshall Barton said:
Just got back from a trip to Microsoft on the west coast.

Sorting a form is differnt from sorting a report. In a form
you can use either the form's Filter property or the form's
record source query.

I don't have enough details to make much in the way of a
suggestion. You said it doesn't work, but I don't know what
you tried nor the result that was produced.
--
Marsh
MVP [MS Access]


Crazy said:
No matter what I do to the underlying query of my form I cannot get the
SortVal to work. It will not sort by area including both the alpha and
numeric values and it's not sorting the Tag exactly either. Strictly
speaking the Tag is alphanumeric, but with an input mask of GL/ followed by
numbers as in GL/0001 or GL/1561. The Form Sort will sort the Tag field
correctly but not the Area field. The SortVal calculated field is not doing
either.

I know I've got this sorted out in a report, but sometimes I will just need
to view the data (with more fields than the report contains). By the way,
have I mentioned that my form is in Data sheet view?


:
But, Lady, that's not "code", it's just a trivial expression
in one line of the Sorting and Grouping window. The second
one is just a field name selected from the field/expression
drop down list. It sounds like you may be unfamiliar with
using Sorting and Grouping (View menu). If so, take a
little time out from making extra work for yourself and get
familiar with this powerful, but easy to use feature of
Access reports. Then give my earlier suggestion a try.

Note that, unlike forms, anything beyond a trivial report
**must** use Sorting and Grouping to specify it's sorting.
Changing this according to user input requires a small
amount of real VBA code. If your up to it, post back with
specific details for a specific report and I'll provide some
code for you to try.

As to your other question, you can use a slightly more
complex expression (not code) to cause your form's record
source **query** to be sorted in either of two ways. First,
some form's control (text box, check box, combo box, etc)
should be used to allow the users to specify which sort they
want. Since you didn't provide much in the way of clues to
your requirements, I can only provide the general idea.
Add a calculated field to the query:
SortVal:IIf(Forms!someform.somecontrol = val1,fieldA,fieldB)


Crazy Lady wrote:
Thanks for replying Marshall. I don't think this helps me, because I don't
know how to use code. I only have one piece of code in my database so far,
and that was given to me on this site.

If you wouldn't mind eleborating I would be very grateful.

You may be able to help further. I am wanting to sort in a report mainly,
but possibly also in a form and there are two possible sort criteria. I may
want to sort by this field or by another. I was intending to create two
reports sorted in those two ways and have a prompt ask which report to show.
Is there a better way?


Crazy Lady wrote:
The numbers represent buildings and are stored as text. The Sort Ascending
and Decending option sorts the numbers only first and then the
numbers/letters. I want the list to end up as 20, 21a, 105c, 200.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top