Form Validation Issue (1 Viewer)

[Juett]

Hi guys,

This might be an already-trodden issue...but does anyone know why this form validation works (in the form properties box):

>="36.5" And <="37.5"

But this doesn't:

>="9.5" And <="10.5"

What am I missing here?

 

[Minty]

Those are strings, and I'm guessing you should be checking for numbers?

e.g. >= 9.5 And <=10.5

 

[Juett]

They are strings, but, the first one still works as if it was a number range, and the latter doesn't, and I can't see why. Shoudn't either both work or both not work?

 

[GaP42]

The comparison of the test values to the set of values you have stored looks at the string char by char to compare in ASCII order - the first compare between "36.5" And "37.5" looks for things that start with "3" then between 6 and 7, then .. the difference at the second character is detected and retrieves values accordingly.
The second comparison of two strings of unequal length: "9.5" And "10.5" - so looking at the first char - >"9" to <"1", then >="." to <="0" - see the problem? Your human interpretation of the string as a number is not what Access sees because you told it to look at as a string comparison.

 

[Pat Hartman]

to continue the explanation. You can sometimes get strings to compare correctly if you right justify them. But, that would also require that the decimal position be fixed at 1. So, I'm going to use a lower case "b" as the fill character and we'll assume the string length is 5.

bb9.5 will be < b10.5 because "b" is < 1 (the second character - since both first characters are b)

But usually, when you have strings, they are not consistent enough to just right justify them to get the compare or sort to work correctly.