Convert a String to a Number 2

[COLTAB]

I have been struggling with this problem for a few days so I'm hoping someone can help. I have a field which is designed as a string and contains sizes such as 1/4" , 1/2" , 3/4" , 1 1/2" etc...
I need to convert these to a numeric field where I can select the largest size within a group of fields. I have the fraction to decimal conversion done using IIF statments but when I try to enclose this statement within any type of CAST or CDbl statement I get an error. Any ideas??

 

[PHV]

Any chance you could post what you've tried ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[COLTAB]

OK - should have included this in the original post...

To convert the fraction to decimal I have used the following statement:
IIF(LEFT([PSZ],(INSTR([PSZ],'"')-1))="1/2",0.5,(IIF(LEFT([PSZ],(INSTR([PSZ],'"')-1))="1/4","0.25",IIF(LEFT([PSZ],(INSTR([PSZ],'"')-1))="3/4","0.75",IIF(LEFT([PSZ],(INSTR([PSZ],'"')-1))="1 1/2","1.5",LEFT([PSZ],(INSTR([PSZ],'"')-1))))))) AS TEST
This drops the " at the end of the field and grabs the four possible fractions (1/4,1/2,3/4 and 1 1/2) and converts them to decimal amounts.
I am trying to have the field [TEST] created in my query formated as a number so I have tried:
CAST(IIF(LEFT(...as above...)))AS NUMBER)
and
CDbl(IIF(LEFT(...as above...)))

I have also tried to CAST the field as "NUMERIC" and as "DECIMAL" with no luck.

 

[PHV]

And what about this ?
Val(IIf(Left(PSZ,InStr(PSZ,'"')-1)='1/2', '0.5',
IIf(Left(PSZ,InStr(PSZ,'"')-1)='1/4', '0.25',
IIf(Left(PSZ,InStr(PSZ,'"')-1)='3/4', '0.75',
IIf(Left(PSZ,InStr(PSZ,'"')-1)='1 1/2', '1.5',
Left(PSZ,InStr(PSZ,'"')-1)))))) AS TEST


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[COLTAB]

Awesome!

The "Val()" function works! Don't know why I couldn't find it - guess I was too stuck on looking for a way of doing a conversion.

Thanks so much!

 

[MichaelRed]

an amusing if fraught with posabilities (both positive and negative ones!!!) alternative.

Public Function basStr2Val(strValue As String) As Variant

    'Convert a String with embedded value in text form to a Value _
     e.g. "1 1/2" to 1.5

    Dim strNumParts() As String
    Dim strFractParts() As String
    Dim varTempVal As Variant

    'First attempt is just to find a space
    strNumParts = Split(strValue, " ")

    'First should be the whole number,
    varTempVal = CDbl(strNumParts(0))

    'and the Second is just a fraction
    strFractParts = Split(strNumParts(1), "/")
    varTempVal = varTempVal + CDbl(strFractParts(0) / strFractParts(1))

    basStr2Val = varTempVal

End Function

I can only guess at the mayhem this can (WILL!!!!!) cause when inflicted on the unsuspecting dataset. A WHOLE lot of error checking and extension of the concept would certainly be necessary for any useful implementation. ONE simple example might be to send the string "1 and 1/2" - OOPS and MUCh MUCH more





MichaelRed