Text Field Must Be 17 Characters

[Listerino]

Hi all the Access Experts! (And fellow novices),
I have a question I am hoping someone can solve for me.
I am making a database for my company to be the customer database. We sell RV's and one of the fields in the database is the VIN number. The VIN number should always be 17 characters long and I'm trying to work out a formula to stick in the Validation Rule that will warn me if I've only put in say 16 characters so I know I've missed a character.
Is there any formula that would work? Some kind of text count with an if function maybe?

Try and keep any reply in as simple a way as possible as I'm not too knowledgeable when it comes to formulas.

Any help or ideas appreciated.

Regards,
Lis

 

[DCrake]

Use an input mask

or

After update event

If Len(Trim([field])) <> 17 then
    Bad
Else
    Good
End If

 

[vbaInet]

The VIN number should always be 17 characters long and I'm trying to work out a formula to stick in the Validation Rule that will warn me if I've only put in say 16 characters so I know I've missed a character.

Re Validation Rule, it will be:
Len([Field]) = 17

 

[Listerino]

Thanks guys that second coding worked perfectly!

 

[DCrake]

Just to add to this thread if you revisit it is that VIN's are normally 17 digit numbers, just checking for the entry to 17 characters in lenght will not validate it sufficiently, you may need an additional check to test for a complee number string

IsNumeric(YourField)


My initial suggestion of using an input mask can prevent the user from entering invalid characters/symbols/etc into the field.

Input Mask:99999999999999999;;_

 

[vbaInet]

Thanks guys that second coding worked perfectly!

That was just for your edification, it's not the best way to validate data. DCrake has explained what you should do.

 

[John Big Booty]

A VIN number is actually a mixture of alpha and numeric characters, the article here has a section on validating VIN numbers using the check digit.

 

[DCrake]

I have put together from a VB App a VIN validation checker that uses the correct alogrithmn. There is supporting documentation included for your information.

I will also post seperately into the Sample Databases section as well.

 

[John Big Booty]

Nice little app