text with fractions sort

L

LMV

I have a list of items this is just a sample:

(C)ADAPTER FEMALE 1/2"
(C)ADAPTER FEMALE 3/4"
(C)ADAPTER FEMALE 1"
(C)ADAPTER FEMALE 2"
(C)ADAPTER FEMALE 3"

I want it to sort as above but it sorts
(C)ADAPTER FEMALE 1"
(C)ADAPTER FEMALE 1/2"
(C)ADAPTER FEMALE 2"
(C)ADAPTER FEMALE 3"
(C)ADAPTER FEMALE 3/4"

I understand why I just don't have a solution to get it to sort the way I
want.
Any ideas?

Thanks
 
J

Jeff Boyce

I suspect that since you understand why, you also have a lead on a solution.
Access sorts by numbers and/or letters. If you have a particular sort order
you want for a set of rows, you'll need to give Access something it can use
to correctly sort.

A couple examples ...

In your example, if your table also held a "Dimension" number, Access could
sort by that field.

If you wanted TOTAL control over sort order, you could add a SortBy field
and manually keep it updated with the correct sort order (a LOT more work!).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Douglas J. Steele

What you're showing is text, and it's sorting correctly for text.

There's no simple way to do what you want. You could store the data as
multiple fields ("(C)ADAPTER FEMALE" only as one field, the size as text
without the uom " as another field, and the uom " as a third field). You
could then have a computed field that concatenates the 3 fields together for
display purposes, and sort of Field1, Eval(Field2).

Another alternative would be to have a Sort field in your list.
 
J

jbguernsey

I have a list of items this is just a sample:

(C)ADAPTER FEMALE 1/2"
(C)ADAPTER FEMALE 3/4"
(C)ADAPTER FEMALE 1"
(C)ADAPTER FEMALE 2"
(C)ADAPTER FEMALE 3"

I want it to sort as above but it sorts
(C)ADAPTER FEMALE 1"
(C)ADAPTER FEMALE 1/2"
(C)ADAPTER FEMALE 2"
(C)ADAPTER FEMALE 3"
(C)ADAPTER FEMALE 3/4"

I understand why I just don't have a solution to get it to sort the way I
want.
Any ideas?

Thanks
Click to expand...

For data that looks like this:

(C)ADAPTER FEMALE 1/2"
(C)ADAPTER FEMALE 3/4"
(C)ADAPTER FEMALE 1"
(C)ADAPTER FEMALE 2"
(C)ADAPTER FEMALE 3"

The following solution depends on there being a space (at least one
space) immediately before the numerical part of the data.

You'll need to be able to create and use a function to understand
this.

It works like this ...

(I have kept it a simple as I can and deliberately used some extra
variables to try to make it clearer what is going on)

You need a numeric field (column) in your table (I called the table
tblTestData) set to be Number and Double.

I called this TestValue.

I have assumed the data is in a field called TestText.

Now, for each row of data ...

find the length of the data string

start at the right hand end and step back through the string until you
reach a space character

the numeric part of the string is all the stuff from the space to the
right hand end of the string

use the Eval function to convert this to a number and store it in the
TestValue field.

There is NO error checking built into this small function!
You can then sort on the TestValue field to get the order you want.

Public Function GetValueFromText()

Dim db As database
Dim rs As Recordset

Dim xText As String
Dim n As Long
Dim xCount As Long
Dim xTextValue As Double
Dim xPart As String

Set db = CurrentDb
Set rs = db.openrecordset("tblTestData", dbopendynaset)
rs.MoveFirst

Do Until rs.EOF
'Find the length of the string
n = Len(rs!TestText)
'now step (backwards) through the string from the right hand end
looking for a space character
For xCount = n To 1 Step -1
xPart = Right(rs!TestText, xCount)
'is the first character in xPart a space?
If Left(xPart, 1) = " " Then
rs.Edit
rs!TestValue = Eval(xPart)
rs.Update
End If
Next
rs.MoveNext
Loop

End Function
 
L

LMV

Thanks for the help!

[email protected] said:
For data that looks like this:

(C)ADAPTER FEMALE 1/2"
(C)ADAPTER FEMALE 3/4"
(C)ADAPTER FEMALE 1"
(C)ADAPTER FEMALE 2"
(C)ADAPTER FEMALE 3"

The following solution depends on there being a space (at least one
space) immediately before the numerical part of the data.

You'll need to be able to create and use a function to understand
this.

It works like this ...

(I have kept it a simple as I can and deliberately used some extra
variables to try to make it clearer what is going on)

You need a numeric field (column) in your table (I called the table
tblTestData) set to be Number and Double.

I called this TestValue.

I have assumed the data is in a field called TestText.

Now, for each row of data ...

find the length of the data string

start at the right hand end and step back through the string until you
reach a space character

the numeric part of the string is all the stuff from the space to the
right hand end of the string

use the Eval function to convert this to a number and store it in the
TestValue field.

There is NO error checking built into this small function!
You can then sort on the TestValue field to get the order you want.

Public Function GetValueFromText()

Dim db As database
Dim rs As Recordset

Dim xText As String
Dim n As Long
Dim xCount As Long
Dim xTextValue As Double
Dim xPart As String

Set db = CurrentDb
Set rs = db.openrecordset("tblTestData", dbopendynaset)
rs.MoveFirst

Do Until rs.EOF
'Find the length of the string
n = Len(rs!TestText)
'now step (backwards) through the string from the right hand end
looking for a space character
For xCount = n To 1 Step -1
xPart = Right(rs!TestText, xCount)
'is the first character in xPart a space?
If Left(xPart, 1) = " " Then
rs.Edit
rs!TestValue = Eval(xPart)
rs.Update
End If
Next
rs.MoveNext
Loop

End Function
Click to expand...
 
Top