Problem in compare text-type with a number

A

AndreasM

I must compare a string field, which has value from 1 to 10 or a standard
string value "****".
When i go to the expression builder i wrote:
IIf(IsNumeric([Student list]![1 test]);iif([Student list]![1
test]>5;"Ok";"Bad");[Student list]![1 test]). But the result is #Error.
What`s up? What is the right way to write this comparison?
 
J

John Spencer (MVP)

First thing, I would try replacing the "!" with ".". The period is the correct
separator for tables and fields.

IIf(IsNumeric([Student list].[1 test]);
IIf([Student list].[1 test]>5;"Ok";"Bad");[Student list].[1 test])

Next check the spelling of your fields and table names.

IF that doesn't solve the problem, try breaking this down step by step until you
see where it fails

IIf(IsNumeric([Student list].[1 test]);"Yes";"No")

IF that works, then add in the false response.

If that works then add in the true response.
 
A

AndreasM

Thank you for your answer but the things are still the same. So the ! between
fields and table comes automatic from access. Then i already try to write
IIf(IsNumeric([Student list].[1 test]);"Yes";"No") and its works perfect.
The problem is appearing when i insert < or > or = sign.
 
J

John Spencer (MVP)

Ok, then the problem is that you need to convert the string value to a number.
I should have caught that earlier.


IIf(IsNumeric([Student list].[1 test]);
IIf(Val([Student list].[1 test])>5;"Ok";"Bad");[Student list].[1 test])

OR


IIf(IsNumeric([Student list].[1 test]);
IIf(CInt([Student list].[1 test])>5;"Ok";"Bad");[Student list].[1 test])
 
Top