I need to check for appropriate values entered in a (text) field. The values are UK grid references, eg. SJ89950808, i.e.
The string is 10 characters in length
The first 2 characters are letters, not numbers
The rest (8) should be numbers – but I’ve just being trying to test the last character.
I’ve got a query and have been trying to use the following criteria:
(Len([GridRef])=10) And (Not IsNumeric(Left([GridRef],2))) And (IsNumeric(Right([GridRef],1)))
This returns no rows, with the problem seeming to be testing IsNumeric for the last character on the right as, if I remove it, I get records returned, albeit ones that don’t meet the necessary criteria. If I try to use the IsNumeric test on its own I also get no records.
I’m sure I’m being stupid but I just can’t get this to work.