Registration plate

[Haahz]

Hi, I'm having trouble validating a registration plate field for cars. I need it to do the following rules:
1st Letter cannot be I,J,Q,T,U,Z,
2nd Letter cannot be I,Q,Z,
Last Letter cannot be I or Z
Also how will i make it so it can include both old and new systems, old would be something like this A 123 BCD whereas the new AB 52 DVL could look like that.


Thanks a lot!

 

[Brianwarnock]

Does the 2nd letter comment apply to the old system, ie the 1st letter after the numbers?

Brian

 

[Haahz]

Yes it does.

 

[gemma-the-husky]

and what about old style plates, suffix instead of prefix, and even no suffix/prefix at all (old cherished stuff)

perhaps easier not to bother about validating formats - just make sure the number is unique, and dont use the regno as a primary key (or even a foreign key) then you can change it if needed

personally i would remove any embedded spaces, for consistency

so AB01CDE
or A123DEF

etc

 

[Haahz]

How would I make sure the number is unique without doing it manually?

 

[gemma-the-husky]

make it a unique keyed field in your table - but DONT make it the primary key - have an autonumber field for that

 

[Brianwarnock]

Hmm, ok is the number of letters variable? I'm thinking along the lines of parsing the registration plate based on a check for mid([plate],2,1)=" "

newfield =IIF(mid([plate],2,1)=" ",Left([plate],1),Left([plate],2)) & Right([plat],3)

Then doing the tests on the result.

Brian

 

[Haahz]

Hmm, ok is the number of letters variable? I'm thinking along the lines of parsing the registration plate based on a check for mid([plate],2,1)=" "

newfield =IIF(mid([plate],2,1)=" ",Left([plate],1),Left([plate],2)) & Right([plat],3)

Then doing the tests on the result.

Brian

Yeah they are.

 

[Brianwarnock]

It seems to me that it is not going to be worth trying to build a string so that you can just use one piece of code for all tests, however the 1st and last can be common
If Left([Plate],1) = "I" or Left([Plate],1)= "J" or etc Then
Error

I don't think that you can use the IN function in code.

For Last you use the Right function

Then If Mid([plate],2,1)<> " " run the tests on that , else
Instrrev([plate]," ")+1 will give you the position of the 2nd letter.

Brian