Trim "<" from text and convert to a number

[millrat]

Hi all,

I have numbers stored as text in a table because some have a "<" sign in front of them.
How can I trim the "<" from the front (for those that have it) in a select query and convert to a number?

eg <0.05 (text) to 0.05 (number)

Cheers,
millrat

 

[Remou]

How about:

[tt]IIf(Instr(strNumber,"<")>0,Val(Mid(strNumber,2)),strNumber)[/tt]

 

[genomon]

Or : Format(CSng(REPLACE(strNumber,"<","")),"####0.00")

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[millrat]

Hi Remou,

My test Table1

Field1
1.1
0.01
<0.05

After plugging this into my test query as citeria I get

SELECT [Table1].[Field1]
FROM Table1
WHERE ((([Table1].[Field1])=IIf(InStr([Field1],"<")>0,Val(Mid([Field1],2)),[Field1])));

This only returns numbers with no "<" sign

ie
1.1
0.01

Is this the right place to use this?

Cheers,

 

[Remou]

You asked for a trim and return as number, not a criteria. You would more likely say:

[tt]SELECT [Table1].[Field1], IIf(InStr([Field1],"<")>0,Val(Mid([Field1],2)),[Field1])
FROM Table1[/tt]

 

[millrat]

Ahhh!!! Silly Me,

This works

SELECT IIf(InStr([Field1],"<")>0,Val(Mid([Field1],2)),[Field1]) AS Expr1
FROM Table1;

So does this

SELECT Format(CSng(REPLACE([Field1],"<","")),"####0.00") AS Expr1
FROM Table1;


Thanks guys