Solved Validation Rule (2 Viewers)

[Russp42]

I have a customer table. The customer ID consists of both letters and numerals. I want a validation rule making the first character a letter

 

[The_Doc_Man]

If this first character is known to be uppercase it is easier. However, for completeness, try this. A validation rule is like an IF clause without the IF, so if the field name is XYZ,...

( INSTR( 1, "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz", LEFT( XYZ, 1 ), vbTextCompare ) > 0 )

Basically, it asks "Is the leftmost - or first - byte in the list of alphabetic characters." NOTE: It is possible that vbTextCompare might not translate and thus give a compile error. If it does that, use 1, which is the value of that symbolic option.

Obviously, if the ID can only be upper case, you can cut down the character list by half. If the field (3rd argument) is empty, INSTR returns 0. If the field is NULL, INSTR returns a NULL, which is not >0.

There ARE other ways to do this, but this one is fairly simple and straight-forward.

 

[Russp42]

Thanks for your reply. What I am trying to achieve is to prevent users adding new customers and giving them a numerical code. All the current codes are based on the first 3 or 4 letters of their Surname but where duplications occur a number is added. Is this the best way of achieving this?

 

[The_Doc_Man]

When you add that statement of purpose to your prior discussion, then... "Is this the best way of achieving this?" Almost certainly not.

The reason I say that is that the implication of doing this via some kind of field validation rule is that your users see a raw table. If that is so, you are hopelessly exposed to all sorts of ills. You have little to no control over what happens when users see tables.

The best way to do this is, when you add a new customer and give them a code, do so though a form where you can do extensive (or simple) VBA operations to do your screening, and you use the form's _BeforeUpdate event to do final validity tests. The _BeforeUpdate event can be canceled (i.e. blocked from completion) which means you can stop entry of a bad code before it happens.

There are various schools of thought here, but most of the experts would agree that an extensive Form_BeforeUpdate routine would be the best way to assure proper formatting for the fields you want to enter. Not just your code, but anything else that you really wanted to be entered at that point. Doing it through a form is going to be the best solution to get maximum flexibility and maximum effect.

There is another school of thought that says when you use a form, put validation code in each control's _LostFocus routine and thus put individual field validation associated with each specific field - whereas doing validation in the _BeforeUpdate routine tests each field in turn and so can get a bit complex. Which way is right? Damned if I know. Opinions differ. But I'm pretty sure that using a field validation code is asking a bit much for what sounds like a potentially complex selection process.

Think about your goals and maybe tell us IN WORDS what you are trying to do. Don't dwell on mechanisms, but tell us desired results.

 

[Russp42]

When you add that statement of purpose to your prior discussion, then... "Is this the best way of achieving this?" Almost certainly not.

The reason I say that is that the implication of doing this via some kind of field validation rule is that your users see a raw table. If that is so, you are hopelessly exposed to all sorts of ills. You have little to no control over what happens when users see tables.

The best way to do this is, when you add a new customer and give them a code, do so though a form where you can do extensive (or simple) VBA operations to do your screening, and you use the form's _BeforeUpdate event to do final validity tests. The _BeforeUpdate event can be canceled (i.e. blocked from completion) which means you can stop entry of a bad code before it happens.

There are various schools of thought here, but most of the experts would agree that an extensive Form_BeforeUpdate routine would be the best way to assure proper formatting for the fields you want to enter. Not just your code, but anything else that you really wanted to be entered at that point. Doing it through a form is going to be the best solution to get maximum flexibility and maximum effect.

There is another school of thought that says when you use a form, put validation code in each control's _LostFocus routine and thus put individual field validation associated with each specific field - whereas doing validation in the _BeforeUpdate routine tests each field in turn and so can get a bit complex. Which way is right? Damned if I know. Opinions differ. But I'm pretty sure that using a field validation code is asking a bit much for what sounds like a potentially complex selection process.

Think about your goals and maybe tell us IN WORDS what you are trying to do. Don't dwell on mechanisms, but tell us desired results.

 

[Russp42]

I have a form for adding new customers. The ID code, Surname, Christian Name and Contact. Some of the staff have been using codes using all numerals in the ID code. Although the ID code can contain both letters and numerals I want to prevent the code starting with a numeral

 

[arnelgp]

you can also Generate the code automatically and prevent user's from entering it.
see frmCustomer.

 

[Gasman]

As @arnelgp states. Do NOT allow the users to create their own codes. Recipe for disaster.

 

[CJ_London]

If users are happy with entering all number codes, what is the benefit of using alternative codes? It’s not sorting since you have the names for that. It’s not filtering for the same reason.

Only thing I can think of is you are limiting the code to 3 characters and have more than 1000 customers so need to extend the codes beyond 999

 

[ebs17]

I want a validation rule making the first character a letter

The safest and most comprehensive approach is to create the validity check in the table definition.

Design view of the table
ValidationRule for IDCode: LIKE "[A-Z]*"
ValidationText: "Give me a letter at first"

 

[Russp42]

The safest and most comprehensive approach is to create the validity check in the table definition.

Design view of the table
ValidationRule for IDCode: LIKE "[A-Z]*"
ValidationText: "Give me a letter at first"

 

[Russp42]

Thanks for that. Seems to be just what I want

 

[arnelgp]

Thanks for that. Seems to be just what I want

what if there is no number entered?

 

[KitaYama]

The safest and most comprehensive approach is to create the validity check in the table definition.

Design view of the table
ValidationRule for IDCode: LIKE "[A-Z]*"
ValidationText: "Give me a letter at first"

Me, as a person who has never worked with Access in depth, have always hated table side validation rules.
I've always wondered what will happen if a year or two from now, me or someone else tries to upsize the database to sql server or Mysql?
Who remembers the validations that are in place, and who will go to the trouble to change them to vba?

But who knows? Maybe professionals like you, have your methods to bypass this problem.

 

[ebs17]

Who remembers the validations that are in place, and who will go to the trouble to change them to vba?

What works at table level and directly in connection with the database engine does not have to be delegated to a front end. Entries in a table can be made in different ways: via bound forms, via action queries, via writing recordsets, via standard imports, via manual entries directly into the table.
You would have to spread your VBA measures over everything every time you access it. This is not practically possible, but it would definitely be a huge effort.
Would you want to remove a unique index from the table definition to avoid unwanted duplicates and implement it using VBA, comprehensively for all later extensions and without errors?

 

[KitaYama]

Would you want to remove a unique index from the table definition to avoid unwanted duplicates and implement it using VBA, comprehensively for all later extensions and without errors?

Not trying to urge you, but indexes can be moved to tables in sql server in upsizing process. I didn't say anything about indexes, but validation rules.
Our discussion was about the rules that can not be used after upsizing to sql server. I wish you had explained that, instead of bringing something that was never a part of the OP's problem.

 

[Minty]

Validation rules on tables become constraints when moved to SQL Server using SSMA.

Whist this sort of works, in reality, they are generally a right royal PITA, as they simply generate a OBDC error if the constraint isn't adhered to.
So you have to go into every form and code in the validation in the before update events to handle then so the ODBC error is kept at bay, or remove the constraints and then still program them on the form as there isn't another way of ensuring data integrity.

 

[KitaYama]

Validation rules on tables become constraints when moved to SQL Server using SSMA.

Whist this sort of works, in reality, they are generally a right royal PITA, as they simply generate a OBDC error if the constraint isn't adhered to.
So you have to go into every form and code in the validation in the before update events to handle then so the ODBC error is kept at bay, or remove the constraints and then still program them on the form as there isn't another way of ensuring data integrity.

Thanks. I was only waiting for him to admit it.
@Pat Hartman has several times mentioned that she writes her databases in a manner that can be used with least changes after being upsized to sql server and I've always tried to live by this rule. It makes my life easier I think.

 

[ebs17]

SQL CHECK Constraint

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Every database should be able to implement such simple standards if MS Access can.

 

[KitaYama]

SQL CHECK Constraint

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Every database should be able to implement such simple standards if MS Access can.

Thanks. I'll take a look.