Using less than < sign and calculations 1

[millrat]

Hi all,

I need to store some results, some of which use the less than < sign. Do I have to store the data as text? If so how can I convert the text to a number for calculations. Consider the following.

e.g. <0.1
<0.1
<0.1
Sum = 0.3

Cheers,
millrat

 

[Trevil]

Hello,
I think it must be saved as text. Using your example, you can get the 'real' data with a query like:

SELECT IIf(Left([someText],1)='<',Mid([sometext],2)*1,[sometext]*1) AS Stripped
FROM SummaryData;

You obviously could test for something in addition to '<'

Wayne

35+ years of 'progress' -- can't we all just go wire boards again?

 

[lespaul]

I'm pretty sure that the VAL function will work for you as well:

SUM(VAL(FieldName))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Golom]

Leslie

Don't think so. VAL stops parsing as soon as it hits a character that isn't part of a valid number so

[tt]
? VAL("<0.1")
0

? VAL("0.1<")
0.1
[/tt]

You would need something like

VAL ( Mid("<0.1",2) )

 

[lespaul]

I thought that VAL extracted the number where ever it was, didn't realized it worked like you say. My apologies!

les

 

[Golom]

It would probably be nice if it did but then, what would you expect from

Val ( "A1#2#3#4#5K" )

?

No need for apologies. If I apologized for all my mistakes I'd have no time for anything else.

 

[lespaul]

what else???:

12345

makes sense to me!!!

Happy Friday!

les

 

[Golom]

OK ... and

Val ( "1.2.3.4.5" )

?

 

[lespaul]

what else but.....

12345



les

 

[raskew]

The following performs similar to the desired Val() function.

Function SaveNumer(ByVal pStr As String) As Long
'*******************************************
'Purpose:   Removes alpha characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer("A1#2#3#4#5K")
'Output:    12345
'Note:      As written, empty spaces are ignored.
'*******************************************

Dim strHold As String
Dim intLen  As Integer
Dim n       As Integer

    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
          n = n - 1
       End If
       n = n + 1
    Loop Until Mid(strHold, n, 1) = ""
    SaveNumer = val(strHold)

End Function

HTH - Bob