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

[GwenH]

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

 

[KARL DEWEY]

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

 

[GwenH]

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

 

[GwenH]

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.

 

[Klatuu]

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)
--
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
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.
01.01-09
01.01-10
01.01-10a

 

[GwenH]

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)
aftDec: Mid([component_ID],InStr([component_ID],".")+1)
adtDash: Mid([component_ID],InStr([component_ID],"-")+1)
--
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
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.
01.01-09
01.01-10
01.01-10a

--
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:

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

 

[Klatuu]

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)

--
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
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)
aftDec: Mid([component_ID],InStr([component_ID],".")+1)
adtDash: Mid([component_ID],InStr([component_ID],"-")+1)
--
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
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.
01.01-09
01.01-10
01.01-10a

--
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:

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

 

[GwenH]

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.

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)

--
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
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)
aftDec: Mid([component_ID],InStr([component_ID],".")+1)
adtDash: Mid([component_ID],InStr([component_ID],"-")+1)
--
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
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.
01.01-09
01.01-10
01.01-10a

--
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:

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

 

[John W. Vinson]

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

Ideas?

Misplaced close parenthesis: try

SplitString([component_ID],1)

 

[Michael Gramelspacher]

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?