IIf Rule not calculating as it should

[patrickstrijdonck]

Hello,

I have got a strange thing in MS Access, When i use the following rule;

IIf([Count]>="2","True","False")

If Count is 1 it will show "False" = Correct!

If Count is 2 trough 9 it will show "True" = Correct!

If Count is 10 trough 19 it will show "False" = NOT CORRECT!

If Count is 20 and higher it will show "True" = Correct!

If I replace "2" in the rule and replace it with "3", 10 trough 29 will show "False"

It seems that Access is calculating like this

1
11
12
13
14
15
16
17
18
19
2
21
Etc......

Which means that 19 is not higher then 2

Any logical reason for this?
Thanks in advance!

 

[MajP]

yes of course.
[count]>="2"
is a string value not a number so it does a text comparison starting with the left character. So 2 is greater than 19 and 19999999999.
I think you mean
[count]>=2

Also you should never have a field named "count" which is both a reserved word in SQL and in VBA.

 

[patrickstrijdonck]

thanks, the field is not called Count offcourse, I just used that to make it more clear, it's all dutch nonsense

I had already tried to remove the "" but then access gives "The expression you entered contains an invalid syntax"

but I think you are right on the text comparison, as in Text, B is higher then Aa and Aaaaaaaaa etc....

 

[SkipVought]

It seems that Access is calculating like this...

Your field is a TEXT field, as indicated by...

I had already tried to remove the "" but then access gives "The expression you entered contains an invalid syntax"

As such the TEXT VALUE 1 is vastly different than THE NUMBER 1

With text the colatin sequence of NUMERIC DIGITS (TEXT) is...
[tt]
1
11
111
2
22
222
3
33
333
...
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]