sorting text in numeric order

[Deborah V. Gardner]

I have a field with values like this

CO 03-10
CO 03-4
VI 03-8
CO 03-533

I would like these to sort for a report by the first two letters and the
digits after the hyphen (-) like this

CO 03-4
CO 03-10
CO 03-533
VI 03-8

I can get them to sort by the first two letters or the last few digits
but not both. Any ideas?

Thank you,
Deborah

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To sort "text-numbers" as numerics you have to have leading zeros.
E.g.:

CO 03-010
CO 03-004
VI 03-008
CO 03-533

This will sort to:

CO 03-004
CO 03-010
CO 03-533
VI 03-008

You can put leading zeros on text numbers like this:

If strNumber = "2" then Format(strNumber, "000") will produce "002".

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5hhGIechKqOuFEgEQKqmQCfY9Uft5LueIPOhFzj4AVbUR lCrkgAn0j9
xrYKe+6yzXpzFROW3tXqFIwk
=ii4g
-----END PGP SIGNATURE-----

Deborah V. Gardner wrote:

I have a field with values like this

CO 03-10
CO 03-4
VI 03-8
CO 03-533

I would like these to sort for a report by the first two letters and the
digits after the hyphen (-) like this

CO 03-4
CO 03-10
CO 03-533
VI 03-8

I can get them to sort by the first two letters or the last few digits
but not both. Any ideas?

Thank you,
Deborah

[Gary Floam]

"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:3F***************@twcny.rr.com...

I have a field with values like this

CO 03-10
CO 03-4
VI 03-8
CO 03-533

I would like these to sort for a report by the first two letters and the
digits after the hyphen (-) like this

CO 03-4
CO 03-10
CO 03-533
VI 03-8

I can get them to sort by the first two letters or the last few digits
but not both. Any ideas?

Thank you,
Deborah

First you will have to use a query. In the query you will need to reference
a function that has to be in some module. The function should look
something like

Function SortFunction(InputString as String) as String
Dim DashPosition as Integer
Dim FirstPart as String
Dim LastPart as String
LastPartFormatted as String
DashPosition = Instr(InputString,"-")
FirstPart = Left(InputString, 2)
LastPart = Mid(InputString, DashPosition + 1)
LastPartFormatted = Format(Val(LastPart),"000")
SortFunction = FirstPart & LastPart

In the query you will use SortFunction(FieldName) as a field in the query,
and then sort on it.

Hope this helps,

Gary

[Deborah V. Gardner]

Thank you for the suggestions. I really wanted to avoid padding the numbers with
zeroes as you and MG Foster suggested. So far, this is what I have.

Dim intPostion As Integer, intLength As Integer, intLast As Integer
Dim intLastNo As Integer

intPosition = InStr(InputString, "-")
intLength = Len(InputString)
intLast = intLength - intPosition
xSortLast = Right(InputString, intLast)

It works very well putting the items in numberical order. So, I tried to include
it in a Union query. First I built two queries, 1 for each table and used Order
By like this
Left([ComplaintNo],2), xSortLast([ComplaintNo])
(I replaced ComplaintNo with ViolationNo in the query for tblViolations)

Each query worked perfectly and put the records in numerical order by the last
few numbers. When I put the queries together they do not sort properly. I have
tried putting the Order By clause with the first half of the query; the second
half and both parts. No luck.

Any suggestions? The only other thing I can think of to do is to write the
records to a temporary table and sort that. I am hoping there is an easy method.

Thank you all once again.

Deborah

Gary Floam wrote:

"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:3F***************@twcny.rr.com...

I have a field with values like this

CO 03-10
CO 03-4
VI 03-8
CO 03-533

I would like these to sort for a report by the first two letters and the
digits after the hyphen (-) like this

CO 03-4
CO 03-10
CO 03-533
VI 03-8

I can get them to sort by the first two letters or the last few digits
but not both. Any ideas?

Thank you,
Deborah

First you will have to use a query. In the query you will need to reference
a function that has to be in some module. The function should look
something like

Function SortFunction(InputString as String) as String
Dim DashPosition as Integer
Dim FirstPart as String
Dim LastPart as String
LastPartFormatted as String
DashPosition = Instr(InputString,"-")
FirstPart = Left(InputString, 2)
LastPart = Mid(InputString, DashPosition + 1)
LastPartFormatted = Format(Val(LastPart),"000")
SortFunction = FirstPart & LastPart

In the query you will use SortFunction(FieldName) as a field in the query,
and then sort on it.

Hope this helps,

Gary

[Douglas J. Steele]

I haven't been following this thread, so I'm not sure what you have and
haven't tried. Have you tried adding a computed column to your query that
pads the numbers with zeroes and sorting on that field?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:3F***************@twcny.rr.com...

Thank you for the suggestions. I really wanted to avoid padding the numbers with zeroes as you and MG Foster suggested. So far, this is what I have.

Dim intPostion As Integer, intLength As Integer, intLast As Integer
Dim intLastNo As Integer

intPosition = InStr(InputString, "-")
intLength = Len(InputString)
intLast = intLength - intPosition
xSortLast = Right(InputString, intLast)

It works very well putting the items in numberical order. So, I tried to include it in a Union query. First I built two queries, 1 for each table and used Order By like this
Left([ComplaintNo],2), xSortLast([ComplaintNo])
(I replaced ComplaintNo with ViolationNo in the query for tblViolations)
Each query worked perfectly and put the records in numerical order by the last few numbers. When I put the queries together they do not sort properly. I have tried putting the Order By clause with the first half of the query; the second half and both parts. No luck.

Any suggestions? The only other thing I can think of to do is to write the
records to a temporary table and sort that. I am hoping there is an easy method.
Thank you all once again.

Deborah

Gary Floam wrote:

"Deborah V. Gardner" <dg******@twcny.rr.com> wrote in message
news:3F***************@twcny.rr.com...

I have a field with values like this

CO 03-10
CO 03-4
VI 03-8
CO 03-533

I would like these to sort for a report by the first two letters and the digits after the hyphen (-) like this

CO 03-4
CO 03-10
CO 03-533
VI 03-8

I can get them to sort by the first two letters or the last few digits
but not both. Any ideas?

Thank you,
Deborah

First you will have to use a query. In the query you will need to reference a function that has to be in some module. The function should look
something like

Function SortFunction(InputString as String) as String
Dim DashPosition as Integer
Dim FirstPart as String
Dim LastPart as String
LastPartFormatted as String
DashPosition = Instr(InputString,"-")
FirstPart = Left(InputString, 2)
LastPart = Mid(InputString, DashPosition + 1)
LastPartFormatted = Format(Val(LastPart),"000")
SortFunction = FirstPart & LastPart

In the query you will use SortFunction(FieldName) as a field in the query, and then sort on it.

Hope this helps,

Gary