If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
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 |
#2
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
Kelly wrote:
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 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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
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" wrote: Kelly wrote: 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 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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
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. -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
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" wrote: 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. -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
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 Kelly wrote: 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" wrote: |
#7
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
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] Kelly wrote: 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" wrote: 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. |
#8
|
|||
|
|||
Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
Thanks John and Marshall
-- Kelly "Marshall Barton" wrote: 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] Kelly wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|