converting text to numeric fraction

[Amir]

Hi!

I have a qustion about converting text values to numeric values in a
specified way.
How can I make a query that will convert string typed fractions like "4/10",
"1/3", and the text "full" to the following number typed values:
0.4, 0.3333.., 1 (1 is for the string "full").

How can I create a query that will do that automatically?

Here are the detailes if you're interested:
============================================================================
I have an imported table that has a text field called 'fraction', and field
called 'All', with values like these:
Fraction All
"2/4" 2
"1/2" 1
"7/8" 4
"5/14" 7
"full" 9
"3/9" 4

The rules are as following:
1. The value in the 'All' field are always half of the number written in the
right side of the '/' in the Fraction field.
2. If it's an odd number, then the number in the All field is half minus
0.5.
3. "full" means 1 (equals to 18/18 in the line I wrote), I hope you
understand why.. If not I would be happy to explain.
4. the Fraction field is a text type field.
5. the All field is an interger type field.
6. I have no control over the original table since it's an imported table.

What I wish to do is to convert the values in the Fraction field to number
values like as followed:
"2/4" - will become 0.5
"1/2" -> 0.5
"7/8" -> 0.875
"5/14" -> 0.357142857...
"full" -> 1
"3/9" -> 0.33333..
============================================================================

I would be grateful if you could answer!

Regards,
Amir.

 

[Steve Huff]

Assuming the field is actually called fraction make the field this:

dec: IIf([fraction]="full",1,Eval([fraction]))

you can do a make query to create a new table with the values.

Here is the SQL to take Table1 and make tblOutput for you purposes:

SELECT IIf([fraction]="full",1,Eval([fraction])) AS [dec], Table1.[all] INTO
tblOutput
FROM Table1;

Hope that helps...

 

[Dirk Goldgar]

Hi!

I have a qustion about converting text values to numeric values in a
specified way.
How can I make a query that will convert string typed fractions like
"4/10", "1/3", and the text "full" to the following number typed
values:
0.4, 0.3333.., 1 (1 is for the string "full").

How can I create a query that will do that automatically?

Here are the detailes if you're interested:
========================================================================
====
I have an imported table that has a text field called 'fraction', and
field called 'All', with values like these:
Fraction All
"2/4" 2
"1/2" 1
"7/8" 4
"5/14" 7
"full" 9
"3/9" 4

The rules are as following:
1. The value in the 'All' field are always half of the number written
in the right side of the '/' in the Fraction field.
2. If it's an odd number, then the number in the All field is half
minus
0.5.
3. "full" means 1 (equals to 18/18 in the line I wrote), I hope you
understand why.. If not I would be happy to explain.
4. the Fraction field is a text type field.
5. the All field is an interger type field.
6. I have no control over the original table since it's an imported
table.

What I wish to do is to convert the values in the Fraction field to
number values like as followed:
"2/4" - will become 0.5
"1/2" -> 0.5
"7/8" -> 0.875
"5/14" -> 0.357142857...
"full" -> 1
"3/9" -> 0.33333..
========================================================================
====

I would be grateful if you could answer!

Regards,
Amir.

Suppose you write a function like this and put it in a standard module:

'------ start of function code ------
Function FractionToDecimal(pvFraction As Variant) As Variant

' Convert a fraction passed as a string value (e.g., "1/3")
' into its floating-point equivalent. Returns Double or Null.

Dim strParts() As String
Dim lngNumerator As Long
Dim lngDenominator As Long

If IsNull(pvFraction) Then Exit Function

If pvFraction = "full" Then
FractionToDecimal = CDbl(1)
Exit Function
End If

strParts = Split(pvFraction, "/")

Select Case UBound(strParts)

Case 0
If IsNumeric(strParts(0)) Then
FractionToDecimal = CDbl(strParts(0))
Else
Err.Raise 5
End If

Case 1
If IsNumeric(strParts(0)) Then
lngNumerator = CLng(strParts(0))
Else
Err.Raise 5
End If

If IsNumeric(strParts(1)) Then
lngDenominator = CLng(strParts(1))
Else
Err.Raise 5
End If

If lngDenominator = 0 Then
Err.Raise 5
Else
FractionToDecimal = lngNumerator / lngDenominator
End If

Case Else
Err.Raise 5

End Select

End Function
'------ end of function code ------

Then you could have a query that selects and converts the fraction like
this:

SELECT *, FractionToDecimal([Fraction])
FROM ImportedTable;

If you want to convert and store the result in the table, add a field
"ConvertedFraction" of type Number/Double to the table and execute an
update query like this:

UPDATE ImportedTable
SET ConvertedFraction = FractionToDecimal([Fraction]);

 

[Steve Huff]

Dirk,
The Eval function will evaluate string expressions as if the string were
an actual expression and return the answer, there is no need to write a
custom function for it when the only special case is the word "Full".
--
_______________________
Steve Huff
http://www.huffs.us
Generic email: (e-mail address removed)

Hi!

I have a qustion about converting text values to numeric values in a
specified way.
How can I make a query that will convert string typed fractions like
"4/10", "1/3", and the text "full" to the following number typed
values:
0.4, 0.3333.., 1 (1 is for the string "full").

How can I create a query that will do that automatically?

Here are the detailes if you're interested:
========================================================================
====
I have an imported table that has a text field called 'fraction', and
field called 'All', with values like these:
Fraction All
"2/4" 2
"1/2" 1
"7/8" 4
"5/14" 7
"full" 9
"3/9" 4

The rules are as following:
1. The value in the 'All' field are always half of the number written
in the right side of the '/' in the Fraction field.
2. If it's an odd number, then the number in the All field is half
minus
0.5.
3. "full" means 1 (equals to 18/18 in the line I wrote), I hope you
understand why.. If not I would be happy to explain.
4. the Fraction field is a text type field.
5. the All field is an interger type field.
6. I have no control over the original table since it's an imported
table.

What I wish to do is to convert the values in the Fraction field to
number values like as followed:
"2/4" - will become 0.5
"1/2" -> 0.5
"7/8" -> 0.875
"5/14" -> 0.357142857...
"full" -> 1
"3/9" -> 0.33333..
========================================================================
====

I would be grateful if you could answer!

Regards,
Amir.

Suppose you write a function like this and put it in a standard module:

'------ start of function code ------
Function FractionToDecimal(pvFraction As Variant) As Variant

' Convert a fraction passed as a string value (e.g., "1/3")
' into its floating-point equivalent. Returns Double or Null.

Dim strParts() As String
Dim lngNumerator As Long
Dim lngDenominator As Long

If IsNull(pvFraction) Then Exit Function

If pvFraction = "full" Then
FractionToDecimal = CDbl(1)
Exit Function
End If

strParts = Split(pvFraction, "/")

Select Case UBound(strParts)

Case 0
If IsNumeric(strParts(0)) Then
FractionToDecimal = CDbl(strParts(0))
Else
Err.Raise 5
End If

Case 1
If IsNumeric(strParts(0)) Then
lngNumerator = CLng(strParts(0))
Else
Err.Raise 5
End If

If IsNumeric(strParts(1)) Then
lngDenominator = CLng(strParts(1))
Else
Err.Raise 5
End If

If lngDenominator = 0 Then
Err.Raise 5
Else
FractionToDecimal = lngNumerator / lngDenominator
End If

Case Else
Err.Raise 5

End Select

End Function
'------ end of function code ------

Then you could have a query that selects and converts the fraction like
this:

SELECT *, FractionToDecimal([Fraction])
FROM ImportedTable;

If you want to convert and store the result in the table, add a field
"ConvertedFraction" of type Number/Double to the table and execute an
update query like this:

UPDATE ImportedTable
SET ConvertedFraction = FractionToDecimal([Fraction]);

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
The Eval function will evaluate string expressions as if the
string were an actual expression and return the answer, there is no
need to write a custom function for it when the only special case is
the word "Full".

I'm well aware of that, Steve. But the Eval function won't catch all
the possible errors; for example,

?eval("1/3/4")
8.33333333333333E-02

Not to mention potentially dangerous expressions like Eval("Shell(
....)").

 

[Amir]

Dirk and Steeve,

Thank you both very much!!

Dirk, I have some questions about your answer:

1. what is the syntax of the "Split" and "UBound" functions and what exactly
they do? (Maybe that'l answer my second question too..)
(I understand the "Split" splits a string into an array using it's second
argument to decide where to split.)
2. What is the meaning of the cases that "Case 1" and "Case 0" are supposed
to deal with in the solution you wrote?
3. What does Err.Raise 5 does?


Thank you very much!,

Kind Regards,
Amir.

 

[Dirk Goldgar]

Dirk and Steeve,

Thank you both very much!!

Dirk, I have some questions about your answer:

1. what is the syntax of the "Split" and "UBound" functions and what
exactly they do? (Maybe that'l answer my second question too..)
(I understand the "Split" splits a string into an array using it's
second argument to decide where to split.)

That's a pretty good summary of Split(), though it has a couple of
other, optional, arguments. You'll find it in the online help in the VB
Editor.

UBound() takes an array argument and returns the upper bound of that
array. It can use a second, optional argument to tell it which
dimension of a multidimensional array you want to know about, but the
default dimension is the first (or in this case, only) dimension.
UBound() is handy to use after creating an array with the Split()
function, since you don't generally know in advance how many elements
will be in the array. UBound will tell you -- the array will have
UBound()+1 elements, since its first element will be element 0. There's
a corresponding function LBound() that returns the lower bound of the
array.

2. What is the meaning of the cases that "Case 1" and "Case 0" are
supposed to deal with in the solution you wrote?

The Select statement is examining UBound(strParts). Case 0 is the case
when there is only one element in the array; in other words, no slash
(/) was found. Case 1 is the case when there are two elements in the
array, meaning that one slash was found. Any other case would represent
an error.

3. What does Err.Raise 5 does?

The Err object can be used to examine an error that has been raised, or
to raise an error. In this case, the function checks various conditions
to make sure that the arguments it has been given are valid. If they
aren't, the statemnt "Err.Raise 5" is executed to raise one of the
predefined run-time errors: error 5, "Invalid procedure call or
argument". I could have defined my own error code and message, for
example:

Err.Raise vbObjectError + 1, , _
"Invalid input to function FractionToDecimal"

but this one seemed to fit the bill perfectly under the circumstances.

 

[Amir]

Thank you both very much for your help!

Finally I've changed the code a bit, so that it will fit the real meaning of
the data,
because in my case the "0/0" string means 0 (and is not "undefined",
therefore it should not produce an erorr message)

If lngDenominator = 0 Then
FractionToDecimal = 0 'Instead of Err.Raise 5
Else
FractionToDecimal = lngNumerator / lngDenominator
End If


Thank you a lot for the detailed explanations!!
Kind Regards,
Amir.

 

[Dirk Goldgar]

Thank you both very much for your help!

Finally I've changed the code a bit, so that it will fit the real
meaning of the data,
because in my case the "0/0" string means 0 (and is not "undefined",
therefore it should not produce an erorr message)

Now there's a reason for using a custom routine I hadn't thought of!

You're welcome, and good luck with your project.

 

[Steve Huff]

dec: IIf([fraction]="full",1,IIf([fraction]="0/0",0,Eval([fraction])))