I need an Access field validation rule...

  Chris the Ancient 15:30 03 Sep 2009

... that will validate a car registration field.

Allowing for the plethora of styles of car registrations where there are 'old style' registrations, 'new style' registrations, registrations where some letters or numbers may be omitted (for genuine reasons to make a cherished plate), I haven't been able to work out a validation rule for an Access database.

Has anyone ever found - or been sad enough to generate - such a rule? I can't work one out.

  Woolwell 16:28 03 Sep 2009

Interesting problem which I haven't got an immediate solution to. There are a lot of variables - new style, pre-fix letter, suffix letter, old style and within the old style up to 3 letters and up to 4 numbers before or after the letters. Similar problem would arise within Excel wonder if Vog can help?

  Chris the Ancient 16:44 03 Sep 2009

Now you see why my brain hurts and my hair is falling out.

I also hope that Vog sees this! His Excel brain seems to love such problems - but then it would be coding it into Access validation rules. I might be able to do that given a good guide.

  VoG II 16:52 03 Sep 2009

I have no idea why I even opened this thread since I am an Access moron!

Anyway, I think you'll need Regular Expressions - here's an example click here

There are some useful links on Regular Expressions here click here

  Chris the Ancient 17:31 03 Sep 2009

See what happens when you get curious!

An interesting pair of links to those Irish registrations - and the use of regular expressions.

The big 'down side' when it comes to UK registrations is the massive variations that occur as described by Woolwell.

I have tried doing pencil and paper 'maps' of how the variations could occur (I used some of the registrations I have owned and known) and the more I look at it, the less I can see any form of pattern that might be applied. And then the people who may use the database (coz it won't be me) could well really mess it up by not putting correct spaces in correct places! Trying to make a rule that will cope with people who may not understand the form of composition of vehicle registrations looks as though this will be a lost cause.

Methinks that this might be an insoluble problem.

Now... if I could get into the DVLA database, they might have a way ;-)

  Chris the Ancient 17:44 03 Sep 2009

Been poring over my 'grid' of registrations (some real and some imagined).

I see absolutely no way of making a rule that will cover things - even partially.

Therefore, I suppose this is one of those impossible tasks and I'll call it a day and tick it.

  Woolwell 18:26 03 Sep 2009

I did wonder if you could produce a sub-form with an entry for each different variant of registration and then validate that - still complicated.

  Chris the Ancient 18:36 03 Sep 2009

An idea, I'll admit.

But as the end-user may well have no idea about the potential variations, it may cause more problems than it cures.

I can well imagines that these will probably just enter registrations with no spaces at all (in fact, I probably recommend it). Then, if, and only if, I build in a search facility by registration, it should work moderately well.

  Woolwell 18:46 03 Sep 2009

I was thinking along the lines of if a registration looks like AB 09 ABC then click if it looks like A123ABC then click and so on. Possibly a bit messy.

  Chris the Ancient 19:31 03 Sep 2009

Does look a little messy, I agree.

I#ll just keep it simple, methinks.

  Simsy 20:30 03 Sep 2009

an immediate answer, for I too am an Access moron...

However, perhaps it might be made easier with a slightly different approach...

If there is, (can there be?), extra fields then one could contain the year of registration, this could then be used to filter the format possible...

e.g. since 2001 the mainland British registrations have all been of the format "AA 11 ABC". It would, I imagine be easy to validate that.

There could also be a Yes/No field, (A tick?), to indicate whether the format being entered was non-standard. If the value of this was "No" then no validation need be applied... if this were t be acceptable. With the possibility of vanity plates I would guess that you would need to have this covered anyway?

Apologies if this doesn't help!



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Microsoft Surface Book 2 hands-on review – bigger and 5x faster

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?