Sorting fractional values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a field containing fractional numbers with the TEXT data
type. I need to sort the field from smallest to largest values. However, when
sorted, the values end up 1-1/2, 10-1/2, 2-1/2, etc. instead of 1-1/2, 2-1/2,
10-1/2, etc. What can I do to sort correctly?
Thanks.
 
I have a table with a field containing fractional numbers with the TEXT data
type. I need to sort the field from smallest to largest values. However, when
sorted, the values end up 1-1/2, 10-1/2, 2-1/2, etc. instead of 1-1/2, 2-1/2,
10-1/2, etc. What can I do to sort correctly?
Thanks.
Click to expand...


I don't know Ed, that's a real messy one. Text sorts differently to
numbers and I'm not sure there's anything to change that. With text
you get, as you've found out
1
10
101
2

I love that we are all metric and everything is in decimals. I hope
someone in the, US where you are used to fractional data, can help


Cheers,
Brett
 
Sort on the expression: Val([textfieldname])
Click to expand...

Eh? Won't work correctly: from the debug window -

?val("1 3/4")
13

Not that simple. You'll need some VBA code (which I'm sure is out
there somewhere) to parse the text string, find the slash, evaluate
the fraction and combine it with the integer portion.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John said:
Sort on the expression: Val([textfieldname])
Click to expand...
Eh? Won't work correctly: from the debug window -

?val("1 3/4")
13

Not that simple. You'll need some VBA code (which I'm sure is out
there somewhere) to parse the text string, find the slash, evaluate
the fraction and combine it with the integer portion.
Click to expand...

Something like this works for most well-formed strings:

Function ParseFraction(ByVal str As String) As Double
Dim i, j, k
str = Trim(str)
i = InStr(2, str, "-")
If i = 0 Then i = InStr(str, " ")
If i And i < Len(str) Then
j = Val(Left(str, i))
k = Eval(Mid(str, i + 1)) * Sgn(j)
ElseIf IsNumeric(str) Then
j = Val(str)
Else
k = Eval(str)
End If
ParseFraction = j + k
End Function

Then you can add an expression field like
"NumField: ParseFraction([TextField])
and sort on it.

-Greg.
 
Greg,
Thanks for the responses, guys. Greg, the code works but changes the values
to decimal form. Is there possibly a function in Access that converts
decimals into lowest common denominator fractions that could be inserted
either into the procedure or the query to change the query results back into
fractional form?

Gregory Paret said:
John said:
Sort on the expression: Val([textfieldname])
Click to expand...
Eh? Won't work correctly: from the debug window -

?val("1 3/4")
13

Not that simple. You'll need some VBA code (which I'm sure is out
there somewhere) to parse the text string, find the slash, evaluate
the fraction and combine it with the integer portion.
Click to expand...

Something like this works for most well-formed strings:

Function ParseFraction(ByVal str As String) As Double
Dim i, j, k
str = Trim(str)
i = InStr(2, str, "-")
If i = 0 Then i = InStr(str, " ")
If i And i < Len(str) Then
j = Val(Left(str, i))
k = Eval(Mid(str, i + 1)) * Sgn(j)
ElseIf IsNumeric(str) Then
j = Val(str)
Else
k = Eval(str)
End If
ParseFraction = j + k
End Function

Then you can add an expression field like
"NumField: ParseFraction([TextField])
and sort on it.

-Greg.
Click to expand...
 
Greg,
Thanks for the responses, guys. Greg, the code works but changes the values
to decimal form. Is there possibly a function in Access that converts
decimals into lowest common denominator fractions that could be inserted
either into the procedure or the query to change the query results back into
fractional form?
Click to expand...

<stealing Gregory's code with gratitude, and attribution>

I'd suggest using a calculated field for the sorting (and for range
searching, if you need to do so), and keep the text field as is.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Many thanks, sir. The query works as desired.

John Vinson said:
<stealing Gregory's code with gratitude, and attribution>

I'd suggest using a calculated field for the sorting (and for range
searching, if you need to do so), and keep the text field as is.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Gregory said:
John said:
Sort on the expression: Val([textfieldname])
Click to expand...
Eh? Won't work correctly: from the debug window -

?val("1 3/4")
13
Not that simple. You'll need some VBA code (which I'm sure is out
there somewhere) to parse the text string, find the slash, evaluate
the fraction and combine it with the integer portion.
Click to expand...


Something like this works for most well-formed strings:
[Buggy code snipped]

Then you can add an expression field like
"NumField: ParseFraction([TextField])
and sort on it.
Click to expand...

The code I previously posted wouldn't work for strings like "0-11/16", so I've
updated it to:

'
' Parse a string like 1-3/4 into a number 1.75
' The '-' between the whole number and the fraction can be ' ' instead.
' N.B.: Can do interesting things with oddly formed input!
' Author: Greg Paret
'
Function ParseFraction(ByVal str As String) As Double
Dim i, j, k
str = Trim(str)
i = InStr(2, str, "-")
If i = 0 Then i = InStr(str, " ")
If i And i < Len(str) Then
j = Val(Left(str, i))
k = Eval(Mid(str, i + 1)) * IIf(j < 0, -1, 1)
ElseIf IsNumeric(str) Then
j = Val(str)
Else
k = Eval(str)
End If
ParseFraction = j + k
End Function

I hope the original didn't cause too much hair loss.

-Greg.
 
Back
Top