Sorting fractional values

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.
--
Ed

On Thu, 28 Oct 2004 08:27:05 -0700, "ehunter"
wrote:

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

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer

Sort on the expression: Val([textfieldname])


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"ehunter" wrote in message
...
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.
--
Ed

On Fri, 29 Oct 2004 14:12:07 GMT, "PC Datasheet"
wrote:

Sort on the expression: Val([textfieldname])


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 Vinson wrote:
On Fri, 29 Oct 2004 14:12:07 GMT, "PC Datasheet"
wrote:
Sort on the expression: Val([textfieldname])

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.

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

John Vinson wrote:
On Fri, 29 Oct 2004 14:12:07 GMT, "PC Datasheet"
wrote:
Sort on the expression: Val([textfieldname])

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.

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.

On Fri, 29 Oct 2004 17:16:01 -0700, "ehunter"
wrote:

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?

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

On Fri, 29 Oct 2004 17:16:01 -0700, "ehunter"
wrote:

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?

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

Gregory Paret wrote:
John Vinson wrote:

On Fri, 29 Oct 2004 14:12:07 GMT, "PC Datasheet"
wrote:

Sort on the expression: Val([textfieldname])

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.


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.

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.