Using Val to convert a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am linking to tables in another database and querying data which are in
fields which are text based. The data is limited in the query by filters to
datasets that can all be converted to numerics. I have then used "val" in
the query to do the conversion. I have checked that the results are all true
numbers. I can successfully add these coverted results together, but I find
that I cannot use an IIf statement to test if the result is under a critical
value (0.8). I get a data type mismatch error message.

Suggestions for a way forward would be appreciated.

JMCS
 
This works for me ...

SELECT IIf(Val([TestText])<0.8,0,Val([TestText])) AS Expr1
FROM tblTest
WHERE (((IsNumeric([TestText]))=True));

Alternatively, as Val returns zero if the text can not be evaluated as a
number, you could do it this way if you don't need to distinguish records
with strings that evaluate to zero (e.g. "0.0") from records with strings
that don't evaluate to any number at all ...

SELECT IIf(Val([TestText])<0.8,0,Val([TestText])) AS Expr1
FROM tblTest
WHERE (((Val([TestText]))<>0));

If the field containing the text you are evaluating can contain Null values,
you'll need to eliminate those. If the query will only be used within
Access, you can use the Access NZ function ...

SELECT IIf(Val([TestText])<0.8,0,Val([TestText])) AS Expr1
FROM tblTest
WHERE (((Val(NZ([TestText])))<>0));

Alternatively, if you need to use the query outside of Microsoft Access ...

SELECT IIf(Val([TestText])<0.8,0,Val([TestText])) AS Expr1
FROM tblTest
WHERE (((Val("0" & [TestText]))<>0));
 
Back
Top