Sort fields with 1.1-1, 1.1-10a, etc.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GwenH - 07 Mar 2008 20:21 GMT

I have a table with a field that contains the component ID number for 800+
items. The ID numbers are as follows:

1.1-1
1.1-2
1.1-3
1.1-4
1.1-5
1.1-6
1.1-7
1.1-8
1.1-9
1.1-10
1.1-10a

Because this is a text field, Access does not sort these records correctly
when I try to do a sort on this field. Is there a way I can get Access to
still see this field as text, since it contains text, but sort it correctly?
For example, currently 1.1 comes after 1.10 in the sort.

Many thanks,
Gwen Harrison

Reply to this Message

KARL DEWEY - 07 Mar 2008 20:36 GMT

You will need to create a calculated field for sorting. You will need to
add leading zeros as below.
01.01-09
01.01-10
01.01-10a

Signature

KARL DEWEY
Build a little - Test a little

> I have a table with a field that contains the component ID number for 800+
> items. The ID numbers are as follows:
[quoted text clipped - 18 lines]
> Many thanks,
> Gwen Harrison

Reply to this Message

GwenH - 07 Mar 2008 21:03 GMT

Great idea! Thanks. Now I'm having a little trouble with this:

dash: Left([component_ID],InStr([component_ID],".")+1)

I'm trying to extract the characters after the dash in, for example,
"1.1-10a." I got this to work fine:

compNo2: FormatNumber(Left([component_ID],InStr([component_ID],".")+1))

It extracts the "1.1" portion and formats it as "1.10". I don't have to
worry about putting a zero in front, because all the numbers before the
decimal are 1, 2, 3, or 4 - no two-digit numbers before the decimal.

Thanks,
Gwen H

> You will need to create a calculated field for sorting. You will need to
> add leading zeros as below.
[quoted text clipped - 24 lines]
> > Many thanks,
> > Gwen Harrison

Reply to this Message

GwenH - 07 Mar 2008 21:12 GMT

I spoke too soon. This one is successfully extracting the digit before the
decimal. I do not need to format it with a leading zero, because it is always
going to be 1, 2, 3, or 4.

befDec: Left([component_ID],InStr([component_ID],".")-1)

But then I need a field to extract the number after the decimal. This gives
me "1." instead of "2" from 1.2-3a. And this:

aftDec: Left([component_ID],InStr([component_ID],".")-1)

And this gives me the 1 from 1.2-3a instead of the 2.

aftDec: Left([component_ID],InStr([component_ID],"."))

Finally, I am totally lost on how to extract the characters after the -. The
number of characters after the dash is not always going to be finite.
Sometimes there's one, sometimes two, and sometimes three.

> You will need to create a calculated field for sorting. You will need to
> add leading zeros as below.
[quoted text clipped - 24 lines]
> > Many thanks,
> > Gwen Harrison

Reply to this Message

Klatuu - 07 Mar 2008 21:21 GMT

mid(x,instr(x,"-")+1)
befDec: Left([component_ID],InStr([component_ID],".")-1)
aftDec: Mid([component_ID],InStr([component_ID],".")+1)
adtDash: Mid([component_ID],InStr([component_ID],"-")+1)

Signature

Dave Hargis, Microsoft Access MVP

> I spoke too soon. This one is successfully extracting the digit before the
> decimal. I do not need to format it with a leading zero, because it is always
[quoted text clipped - 43 lines]
> > > Many thanks,
> > > Gwen Harrison

Reply to this Message

GwenH - 07 Mar 2008 21:34 GMT

The "befDec" one works fine. However, the "aftDec" one gives me this from
1.4-2: "4-2". The "aftDash" one gives me "1.4-2". Here's what I need the
results to be:

befDec: 1 (this one works)
aftDec: 4
aftDash: 2

Many thanks,
GwenH

> mid(x,instr(x,"-")+1)
> befDec: Left([component_ID],InStr([component_ID],".")-1)
[quoted text clipped - 48 lines]
> > > > Many thanks,
> > > > Gwen Harrison

Reply to this Message

Klatuu - 07 Mar 2008 21:53 GMT

Easier way.

Put this code in a standard module:

Public Function SplitString(ByVal strAll As String, ByVal lngPos As Long) As
String
Dim varSplit As Variant
varSplit = Split(Replace(strAll, "-", "."), ".")
SplitString = varSplit(lngPos - 1)
End Function

Now, in your query:

befDec: SplitString([component_ID],1)
aftDec: SplitString([component_ID],2)
aftDash: SplitString([component_ID],3)

Signature

Dave Hargis, Microsoft Access MVP

> The "befDec" one works fine. However, the "aftDec" one gives me this from
> 1.4-2: "4-2". The "aftDash" one gives me "1.4-2". Here's what I need the
[quoted text clipped - 59 lines]
> > > > > Many thanks,
> > > > > Gwen Harrison

Reply to this Message

GwenH - 08 Mar 2008 19:56 GMT

Okay, I did what you suggested. I pasted the code you provided into a
standard module. I entered no other code with it. Then I modified my query as
you suggested, using copy and paste to copy your code. Now I can't even open
my query. I am getting this error:

Compile error. in query expression 'SplitString([component_ID]),1'.

Ideas?

Gwen H

> Easier way.
>
[quoted text clipped - 76 lines]
> > > > > > Many thanks,
> > > > > > Gwen Harrison

Reply to this Message

John W. Vinson - 08 Mar 2008 21:52 GMT

>Compile error. in query expression 'SplitString([component_ID]),1'.
>
>Ideas?

Misplaced close parenthesis: try

SplitString([component_ID],1)

Signature

John W. Vinson [MVP]

Reply to this Message

Michael Gramelspacher - 09 Mar 2008 12:22 GMT

Public Function SplitString(ByVal strAll As String, _
ByVal lngPos As Long) As String
SplitString = Split(strAll, "-")(lngPos - 1)
End Function

Sub CreateTable()

With DBEngine(0)(0)

.Execute _
"CREATE TABLE Components (" & _
"ComponentID VARCHAR (10) NOT NULL PRIMARY KEY);"

.Execute "INSERT INTO Components VALUES('1.1-1');"
.Execute "INSERT INTO Components VALUES('1.1-1');"
.Execute "INSERT INTO Components VALUES('1.1-2');"
.Execute "INSERT INTO Components VALUES('1.1-3');"
.Execute "INSERT INTO Components VALUES('1.1-4');"
.Execute "INSERT INTO Components VALUES('1.1-5');"
.Execute "INSERT INTO Components VALUES('1.1-6');"
.Execute "INSERT INTO Components VALUES('1.1-7');"
.Execute "INSERT INTO Components VALUES('1.1-8');"
.Execute "INSERT INTO Components VALUES('1.1-9');"
.Execute "INSERT INTO Components VALUES('1.1-10');"
.Execute "INSERT INTO Components VALUES('1.1-10a');"

End With
End Sub

SELECT ComponentID
FROM Components
ORDER BY SplitString([ComponentID],1) & "-" & RIGHT("000" &
SplitString([ComponentID],2),3);

ComponentID
1.1-1
1.1-2
1.1-3
1.1-4
1.1-5
1.1-6
1.1-7
1.1-8
1.1-9
1.1-10
1.1-10a

Is this what you are trying to achieve?

Reply to this Message