Sorting numbers in a text field

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

Guest

I have a computer asset database for which I am trying to sort by the last
three digits in an alphanumeric text field. I am trying to do this within a
report that already sorts by department name.

Here's what I'm looking for. The computer names (for which I am trying to
sort) use the same format as follows:

ADM-GX270-008
ADM-GX100-009
or
HS-D520-034
HS-Ins8600-035 and so on...

Basically it is: "BuildingName-ModelNumber-ComputerNumber"

All I am looking for is the computer number (last 3 digits) and to
completely ignore everything before that. Can this be done? If so, what
kind of formula would work for me?

Any help would be greatly appreciated!
 
KD7ITY said:
I have a computer asset database for which I am trying to sort by the last
three digits in an alphanumeric text field. I am trying to do this within
a
report that already sorts by department name.

Here's what I'm looking for. The computer names (for which I am trying to
sort) use the same format as follows:

ADM-GX270-008
ADM-GX100-009
or
HS-D520-034
HS-Ins8600-035 and so on...

Basically it is: "BuildingName-ModelNumber-ComputerNumber"

All I am looking for is the computer number (last 3 digits) and to
completely ignore everything before that. Can this be done? If so, what
kind of formula would work for me?

Any help would be greatly appreciated!
Click to expand...

In a query, create a calculated field

SortField: Right([YourField], 3)

and sort on that. Replace YourField with the name of the field that
holds your data.

Tom Lake
 
Okay, I will give that a try. Thank you very much Tom.


Tom Lake said:
KD7ITY said:
I have a computer asset database for which I am trying to sort by the last
three digits in an alphanumeric text field. I am trying to do this within
a
report that already sorts by department name.

Here's what I'm looking for. The computer names (for which I am trying to
sort) use the same format as follows:

ADM-GX270-008
ADM-GX100-009
or
HS-D520-034
HS-Ins8600-035 and so on...

Basically it is: "BuildingName-ModelNumber-ComputerNumber"

All I am looking for is the computer number (last 3 digits) and to
completely ignore everything before that. Can this be done? If so, what
kind of formula would work for me?

Any help would be greatly appreciated!
Click to expand...

In a query, create a calculated field

SortField: Right([YourField], 3)

and sort on that. Replace YourField with the name of the field that
holds your data.

Tom Lake
Click to expand...
 
After creating a query with your information, it only displays the last three
digits. It sorts them properly, but doesn't give me the rest of the computer
name.

I guess I could have better worded my original question. I am wanting the
full computer name to be displayed in the report, but only sorted by the last
three digits. Can this be done?

Thanks!


Tom Lake said:
KD7ITY said:
I have a computer asset database for which I am trying to sort by the last
three digits in an alphanumeric text field. I am trying to do this within
a
report that already sorts by department name.

Here's what I'm looking for. The computer names (for which I am trying to
sort) use the same format as follows:

ADM-GX270-008
ADM-GX100-009
or
HS-D520-034
HS-Ins8600-035 and so on...

Basically it is: "BuildingName-ModelNumber-ComputerNumber"

All I am looking for is the computer number (last 3 digits) and to
completely ignore everything before that. Can this be done? If so, what
kind of formula would work for me?

Any help would be greatly appreciated!
Click to expand...

In a query, create a calculated field

SortField: Right([YourField], 3)

and sort on that. Replace YourField with the name of the field that
holds your data.

Tom Lake
Click to expand...
 
Include both the full field and the calculated sort field in the query,.

In the report, use the Sorting and Grouping dialog and sort by the
calculated field, but display the full field

Alternative is to use the sorting and grouping dialog (which you should
do anyway, since sorting in a query is ignored on reports.) In the
sorting and grouping dialog enet

=Right([Your Field],3)

That will then sort by Your Field while you can display the entire field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

After creating a query with your information, it only displays the last three
digits. It sorts them properly, but doesn't give me the rest of the computer
name.

I guess I could have better worded my original question. I am wanting the
full computer name to be displayed in the report, but only sorted by the last
three digits. Can this be done?

Thanks!


Tom Lake said:
KD7ITY said:
I have a computer asset database for which I am trying to sort by the last
three digits in an alphanumeric text field. I am trying to do this within
a
report that already sorts by department name.

Here's what I'm looking for. The computer names (for which I am trying to
sort) use the same format as follows:

ADM-GX270-008
ADM-GX100-009
or
HS-D520-034
HS-Ins8600-035 and so on...

Basically it is: "BuildingName-ModelNumber-ComputerNumber"

All I am looking for is the computer number (last 3 digits) and to
completely ignore everything before that. Can this be done? If so, what
kind of formula would work for me?

Any help would be greatly appreciated!
Click to expand...
In a query, create a calculated field

SortField: Right([YourField], 3)

and sort on that. Replace YourField with the name of the field that
holds your data.

Tom Lake
Click to expand...
Click to expand...
 
It worked! That's exactly what I was looking for.

Thank you very much John.


John Spencer said:
Include both the full field and the calculated sort field in the query,.

In the report, use the Sorting and Grouping dialog and sort by the
calculated field, but display the full field

Alternative is to use the sorting and grouping dialog (which you should
do anyway, since sorting in a query is ignored on reports.) In the
sorting and grouping dialog enet

=Right([Your Field],3)

That will then sort by Your Field while you can display the entire field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

After creating a query with your information, it only displays the last three
digits. It sorts them properly, but doesn't give me the rest of the computer
name.

I guess I could have better worded my original question. I am wanting the
full computer name to be displayed in the report, but only sorted by the last
three digits. Can this be done?

Thanks!


Tom Lake said:
I have a computer asset database for which I am trying to sort by the last
three digits in an alphanumeric text field. I am trying to do this within
a
report that already sorts by department name.

Here's what I'm looking for. The computer names (for which I am trying to
sort) use the same format as follows:

ADM-GX270-008
ADM-GX100-009
or
HS-D520-034
HS-Ins8600-035 and so on...

Basically it is: "BuildingName-ModelNumber-ComputerNumber"

All I am looking for is the computer number (last 3 digits) and to
completely ignore everything before that. Can this be done? If so, what
kind of formula would work for me?

Any help would be greatly appreciated!
In a query, create a calculated field

SortField: Right([YourField], 3)

and sort on that. Replace YourField with the name of the field that
holds your data.

Tom Lake
Click to expand...
Click to expand...
Click to expand...
 
Back
Top