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]

Re: sorting text in numeric order

-----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(strNumbe r, "000") will produce "002".

HTH,

MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP5hhGIechKq OuFEgEQKqmQCfY9 Uft5LueIPOhFzj4 AVbURlCrkgAn0j9
xrYKe+6yzXpzFRO W3tXqFIwk
=ii4g
-----END PGP SIGNATURE-----

Deborah V. Gardner wrote:
[color=blue]
> 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
>
>
>
>[/color]

[Gary Floam]

Re: sorting text in numeric order


"Deborah V. Gardner" <dgardner@twcny .rr.com> wrote in message
news:3F98410B.D 48ABAC1@twcny.r r.com...[color=blue]
> 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
>
>
>
>[/color]

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(In putString as String) as String
Dim DashPosition as Integer
Dim FirstPart as String
Dim LastPart as String
LastPartFormatt ed as String
DashPosition = Instr(InputStri ng,"-")
FirstPart = Left(InputStrin g, 2)
LastPart = Mid(InputString , DashPosition + 1)
LastPartFormatt ed = Format(Val(Last Part),"000")
SortFunction = FirstPart & LastPart

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

Hope this helps,

Gary

[Deborah V. Gardner]

Re: sorting text in numeric order

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(InputStri ng, "-")
intLength = Len(InputString )
intLast = intLength - intPosition
xSortLast = Right(InputStri ng, 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:
[color=blue]
> "Deborah V. Gardner" <dgardner@twcny .rr.com> wrote in message
> news:3F98410B.D 48ABAC1@twcny.r r.com...[color=green]
> > 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
> >
> >
> >
> >[/color]
>
> 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(In putString as String) as String
> Dim DashPosition as Integer
> Dim FirstPart as String
> Dim LastPart as String
> LastPartFormatt ed as String
> DashPosition = Instr(InputStri ng,"-")
> FirstPart = Left(InputStrin g, 2)
> LastPart = Mid(InputString , DashPosition + 1)
> LastPartFormatt ed = Format(Val(Last Part),"000")
> SortFunction = FirstPart & LastPart
>
> In the query you will use SortFunction(Fi eldName) as a field in the query,
> and then sort on it.
>
> Hope this helps,
>
> Gary[/color]

[Douglas J. Steele]

Re: sorting text in numeric order

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" <dgardner@twcny .rr.com> wrote in message
news:3F9ADBCE.C 75C539D@twcny.r r.com...[color=blue]
> Thank you for the suggestions. I really wanted to avoid padding the[/color]
numbers with[color=blue]
> 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(InputStri ng, "-")
> intLength = Len(InputString )
> intLast = intLength - intPosition
> xSortLast = Right(InputStri ng, intLast)
>
> It works very well putting the items in numberical order. So, I tried to[/color]
include[color=blue]
> it in a Union query. First I built two queries, 1 for each table and used[/color]
Order[color=blue]
> By like this
> Left([ComplaintNo],2), xSortLast([ComplaintNo])
> (I replaced ComplaintNo with ViolationNo in the query for[/color]
tblViolations)[color=blue]
>
> Each query worked perfectly and put the records in numerical order by the[/color]
last[color=blue]
> few numbers. When I put the queries together they do not sort properly. I[/color]
have[color=blue]
> tried putting the Order By clause with the first half of the query; the[/color]
second[color=blue]
> 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[/color]
method.[color=blue]
>
> Thank you all once again.
>
> Deborah
>
> Gary Floam wrote:
>[color=green]
> > "Deborah V. Gardner" <dgardner@twcny .rr.com> wrote in message
> > news:3F98410B.D 48ABAC1@twcny.r r.com...[color=darkred]
> > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > 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
> > >
> > >
> > >
> > >[/color]
> >
> > First you will have to use a query. In the query you will need to[/color][/color]
reference[color=blue][color=green]
> > a function that has to be in some module. The function should look
> > something like
> >
> > Function SortFunction(In putString as String) as String
> > Dim DashPosition as Integer
> > Dim FirstPart as String
> > Dim LastPart as String
> > LastPartFormatt ed as String
> > DashPosition = Instr(InputStri ng,"-")
> > FirstPart = Left(InputStrin g, 2)
> > LastPart = Mid(InputString , DashPosition + 1)
> > LastPartFormatt ed = Format(Val(Last Part),"000")
> > SortFunction = FirstPart & LastPart
> >
> > In the query you will use SortFunction(Fi eldName) as a field in the[/color][/color]
query,[color=blue][color=green]
> > and then sort on it.
> >
> > Hope this helps,
> >
> > Gary[/color]
>[/color]