Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

Does anyone know how to sort on a text field with numbers. One of the fields
I'm using requires a combination of letters and numbers and they want it
sorted a specific way. The following is how it sorts now.
11-MO599DCA
12-MO599DC
13-PCB602C1
1AC13-PCB536
1AC13-PCB536A
2-PCB602C3
2-PCB602C3A
2R-TB7
2-TB1
2-TB5
31-C1
31-C2
31-C3
3-1M1
3-1M2
They would prefer it be sorted this way.
1AC13-PCB536
1AC13-PCB536A
2-PCB602C3
2-PCB602C3A
2-TB1
2-TB5
2R-TB7
3-1M1
3-1M2
11-MO599DCA
12-MO599DC
13-PCB602C1
31-C1
31-C2
31-C3

Thanks for any help.
Kelly
 
Kelly said:
Does anyone know how to sort on a text field with numbers. One of the fields
I'm using requires a combination of letters and numbers and they want it
sorted a specific way. The following is how it sorts now.
11-MO599DCA
12-MO599DC
13-PCB602C1
1AC13-PCB536
1AC13-PCB536A
2-PCB602C3
2-PCB602C3A
2R-TB7
2-TB1
2-TB5
31-C1
31-C2
31-C3
3-1M1
3-1M2
They would prefer it be sorted this way.
1AC13-PCB536
1AC13-PCB536A
2-PCB602C3
2-PCB602C3A
2-TB1
2-TB5
2R-TB7
3-1M1
3-1M2
11-MO599DCA
12-MO599DC
13-PCB602C1
31-C1
31-C2
31-C3
Click to expand...


Try inserting another sort level above the field sort using
the expression:
=Val([the text field])

If that doesn't do what you want, please provide a more
detailed explanation about what's wrong.
 
Marshall thanks for the reply. I inserted this in the SQL statement of the
query but still did not sort correctly. Maybe I'm doing something wrong. The
following is the query.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable, Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
ORDER BY Val([CircuitNumber]);

It is an unusual sort in number order and take the letters into account
also. In the example I first posted they are wanting a logical number order
1-31 but Access starts with 11-MO599DCA then 1AC13-PCB536 and 31-C1 comes
before 3-1M1. From what I gather it's a Microsoft issue.

Kelly


Marshall Barton said:
Kelly said:
Does anyone know how to sort on a text field with numbers. One of the fields
I'm using requires a combination of letters and numbers and they want it
sorted a specific way. The following is how it sorts now.
11-MO599DCA
12-MO599DC
13-PCB602C1
1AC13-PCB536
1AC13-PCB536A
2-PCB602C3
2-PCB602C3A
2R-TB7
2-TB1
2-TB5
31-C1
31-C2
31-C3
3-1M1
3-1M2
They would prefer it be sorted this way.
1AC13-PCB536
1AC13-PCB536A
2-PCB602C3
2-PCB602C3A
2-TB1
2-TB5
2R-TB7
3-1M1
3-1M2
11-MO599DCA
12-MO599DC
13-PCB602C1
31-C1
31-C2
31-C3
Click to expand...


Try inserting another sort level above the field sort using
the expression:
=Val([the text field])

If that doesn't do what you want, please provide a more
detailed explanation about what's wrong.
Click to expand...
 
Kelly said:
Marshall thanks for the reply. I inserted this in the SQL statement of the
query but still did not sort correctly. Maybe I'm doing something wrong. The
following is the query.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable, Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
ORDER BY Val([CircuitNumber]);

It is an unusual sort in number order and take the letters into account
also. In the example I first posted they are wanting a logical number order
1-31 but Access starts with 11-MO599DCA then 1AC13-PCB536 and 31-C1 comes
before 3-1M1. From what I gather it's a Microsoft issue.
Click to expand...


It is not a Microsoft issue, it is a common issue with
sorting text (and mixed number/text) values. Those values
are NOT numbers. they are text. If you do not want the
standard dictionary sorting used for text values, you have
to do something to get whatever you do want.

You replaced your sort with my suggestion when I wanted you
to insert it. Try this:

ORDER BY Val(CircuitNumber), CircuitNumber

If that has further issues, provide a very specific
explanation. "still did not sort correctly" is a nearly
clue free staement.
 
I tried your new suggestion and "still did not sort correctly" . To clarify.
I had the same dictionary sort as before. I appreciate your help but it did
not work. Am I placing it in the correct location of the SQL statement query?
I have the field on Ascending in the query and Sorting and Grouping option
set to Ascending. Puzzled.......
Kelly


Marshall Barton said:
Kelly said:
Marshall thanks for the reply. I inserted this in the SQL statement of the
query but still did not sort correctly. Maybe I'm doing something wrong. The
following is the query.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable, Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
ORDER BY Val([CircuitNumber]);

It is an unusual sort in number order and take the letters into account
also. In the example I first posted they are wanting a logical number order
1-31 but Access starts with 11-MO599DCA then 1AC13-PCB536 and 31-C1 comes
before 3-1M1. From what I gather it's a Microsoft issue.
Click to expand...


It is not a Microsoft issue, it is a common issue with
sorting text (and mixed number/text) values. Those values
are NOT numbers. they are text. If you do not want the
standard dictionary sorting used for text values, you have
to do something to get whatever you do want.

You replaced your sort with my suggestion when I wanted you
to insert it. Try this:

ORDER BY Val(CircuitNumber), CircuitNumber

If that has further issues, provide a very specific
explanation. "still did not sort correctly" is a nearly
clue free staement.
Click to expand...
 
Kelly,

To sort a report in that order, you would need to use the report's sorting and
grouping dialog.

++Open the report in design view
++Select Sorting and Grouping from the menu (or ribbon)
++Enter the following in the first row's Field/Expression
=Val([CircuitNumber])
++Enter the following in the second row's Field
CircuitNumber

Close the sorting and grouping dialog, save the report, and try again.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I guess (because this is the report's news group) that I
should have ignored your posted SQL statement.

Because sorting in a report is controled by Sorting and
Grouping, remove the ORDER BY clause from the report's
record source query and use two lines in Sorting and
Grouping

=Val(CircuitNumber)
CircuitNumber
--
Marsh
MVP [MS Access]

I tried your new suggestion and "still did not sort correctly" . To clarify.
I had the same dictionary sort as before. I appreciate your help but it did
not work. Am I placing it in the correct location of the SQL statement query?
I have the field on Ascending in the query and Sorting and Grouping option
set to Ascending. Puzzled.......


Marshall Barton said:
Kelly said:
Marshall thanks for the reply. I inserted this in the SQL statement of the
query but still did not sort correctly. Maybe I'm doing something wrong. The
following is the query.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable, Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
ORDER BY Val([CircuitNumber]);

It is an unusual sort in number order and take the letters into account
also. In the example I first posted they are wanting a logical number order
1-31 but Access starts with 11-MO599DCA then 1AC13-PCB536 and 31-C1 comes
before 3-1M1. From what I gather it's a Microsoft issue.
Click to expand...


It is not a Microsoft issue, it is a common issue with
sorting text (and mixed number/text) values. Those values
are NOT numbers. they are text. If you do not want the
standard dictionary sorting used for text values, you have
to do something to get whatever you do want.

You replaced your sort with my suggestion when I wanted you
to insert it. Try this:

ORDER BY Val(CircuitNumber), CircuitNumber

If that has further issues, provide a very specific
explanation. "still did not sort correctly" is a nearly
clue free staement.
Click to expand...
Click to expand...
 
Thanks John and Marshall
--
Kelly


Marshall Barton said:
I guess (because this is the report's news group) that I
should have ignored your posted SQL statement.

Because sorting in a report is controled by Sorting and
Grouping, remove the ORDER BY clause from the report's
record source query and use two lines in Sorting and
Grouping

=Val(CircuitNumber)
CircuitNumber
--
Marsh
MVP [MS Access]

I tried your new suggestion and "still did not sort correctly" . To clarify.
I had the same dictionary sort as before. I appreciate your help but it did
not work. Am I placing it in the correct location of the SQL statement query?
I have the field on Ascending in the query and Sorting and Grouping option
set to Ascending. Puzzled.......


Marshall Barton said:
Kelly wrote:
Marshall thanks for the reply. I inserted this in the SQL statement of the
query but still did not sort correctly. Maybe I'm doing something wrong. The
following is the query.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable, Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
ORDER BY Val([CircuitNumber]);

It is an unusual sort in number order and take the letters into account
also. In the example I first posted they are wanting a logical number order
1-31 but Access starts with 11-MO599DCA then 1AC13-PCB536 and 31-C1 comes
before 3-1M1. From what I gather it's a Microsoft issue.


It is not a Microsoft issue, it is a common issue with
sorting text (and mixed number/text) values. Those values
are NOT numbers. they are text. If you do not want the
standard dictionary sorting used for text values, you have
to do something to get whatever you do want.

You replaced your sort with my suggestion when I wanted you
to insert it. Try this:

ORDER BY Val(CircuitNumber), CircuitNumber

If that has further issues, provide a very specific
explanation. "still did not sort correctly" is a nearly
clue free staement.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top