Remove < sign and sum number stored as text

[millrat]

Hi,
I have a field in a table with numbers stored as text because some of the records will be entered "<0.1"
How can I remove the "<" and convert to number so I can sum the records?
e.g.
<0.1
<0.1
0.1
Sum = 0.3

Thanks in advance.
millrat

 

[ReluctantDataGuy]

Quick and dirty response. You'll need to adopt this to your own usage.

SumVariable = SumVariable + CDbl(IIf(InStr(1,[FieldName],"<") > 0, Mid([FieldName],InStr(1,[FieldName],"<") + 1),[FieldName]))

If the above isn't exactly right, see help on the InStr, Mid, and IIf functions.

If you sometimes have "<" and sometimes ">", then you can used embedded IIF statements - in this general structure:

IIf(Test, TruePart, IIf(Test, TruePart, IIf(Test, TruePart, ... , FalsePart)))...

So for values "<" and ">" it would look something like:

SumVariable = SumVariable + CDbl(IIf(InStr(1,[FieldName],"<") > 0, Mid([FieldName],InStr(1,[FieldName],"<") + 1), IIf(InStr(1,[FieldName],">") > 0, Mid([FieldName],InStr(1,[FieldName],">") + 1), [FieldName])))

You may want to wrap the whole thing in the Nz() function to handle Nulls as zero. Also, I think the Val() function would work as well as CDbl().

My apologies if there's are any typos in the syntax above (once you plug you're own [FieldName] in). I'm only intending it as a pointer toward a solution.

Oh, and if there's always just one character in front of the number, you could probably simplify the above examples a lot. You can use the IsNumeric function to test for a leading "<" or other non-numeric value. For example:

SumVariable = SumVariable + Val(IIf(IsNumeric([FieldName]), [FieldName], Mid([FieldName, 2)))

Note that this example would work even if the numeric value was something like "<45 plus some other text"

That's because the Val() function drops any text following the numeric part and returns only the number.

I do believe Val() strips off leading spaces, so even if there was a space between "<" and the number, it should work - although I'm not absolutely certain about that off hand...

Good luck,
ReluctantDataGuy