Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- From: Kelly <Kelly@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 28 Aug 2009 11:18:02 -0700
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]
- Follow-Ups:
- Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- From: Marshall Barton
- Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- References:
- Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- From: Kelly
- Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- From: Marshall Barton
- Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- Prev by Date: RE: query help needed
- Next by Date: Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- Previous by thread: Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- Next by thread: Re: Sort 1, 1A, 2,3,4,9,10,10A,11,23,45,
- Index(es):
Relevant Pages
|
