Len Validation Rule

[Guest]

How to make validation rule for textbox; min. 8 and max. 10 characters?
I try with Len function but not work...
Len([Text0])<8 And Len([Text0])>10

Thanks!

 

[ruralguy via AccessMonster.com]

Hi Igor,
You didn't say what event you are using to do the validation. Your code
would need to be OR rather than AND and if you are using the BeforeUpdate
event to validate (which is the usual event for validation then you will need
to use the Text property rather than the default .Value property:
If Len(Me.Text0.Text)<8 OR Len(Me.Text0.Text)>10 Then
MsgBox "At least 8 and no more than 10 characters"
Cancel = True
End If

How to make validation rule for textbox; min. 8 and max. 10 characters?
I try with Len function but not work...
Len([Text0])<8 And Len([Text0])>10

Thanks!

 

[Guest]

I use Validation Rule in form TextBox property.
Error message is in the Validation Text property.

Hi Igor,
You didn't say what event you are using to do the validation. Your code
would need to be OR rather than AND and if you are using the BeforeUpdate
event to validate (which is the usual event for validation then you will need
to use the Text property rather than the default .Value property:
If Len(Me.Text0.Text)<8 OR Len(Me.Text0.Text)>10 Then
MsgBox "At least 8 and no more than 10 characters"
Cancel = True
End If

How to make validation rule for textbox; min. 8 and max. 10 characters?
I try with Len function but not work...
Len([Text0])<8 And Len([Text0])>10

Thanks!

 

[John Spencer]

You rule says that the entry must be 8 or less characters and at the same
time it must be 10 or more characters. That is impossible.

If you want the length to be 8, 9, or 10 characters then the rule would be
Len([Text0])>=8 Or Len([Text0])<=10

If you want the length to be 1 to 7 characters or 11 to any number of
characters the rule would be
Not(Len([Text0])>=8 Or Len([Text0])<=10)

 

[Tom Lake]

You rule says that the entry must be 8 or less characters and at the same
time it must be 10 or more characters. That is impossible.

If you want the length to be 8, 9, or 10 characters then the rule would be
Len([Text0])>=8 Or Len([Text0])<=10

Shouldn't that be an And rather than Or? With Or, a length of 2 would be
legal
since Len([Text0])<= 10 is true.

Tom Lake

 

[John Spencer]

Yes, it should be AND not OR. Too much coffee or too little coffee on my
part.

I had changed my mind on which way to test this and forgot to make changes
to what I had already typed.
Thanks for the catch.

You rule says that the entry must be 8 or less characters and at the same
time it must be 10 or more characters. That is impossible.

If you want the length to be 8, 9, or 10 characters then the rule would
be
Len([Text0])>=8 Or Len([Text0])<=10

Shouldn't that be an And rather than Or? With Or, a length of 2 would be
legal
since Len([Text0])<= 10 is true.

Tom Lake

 

[ruralguy via AccessMonster.com]

Igor, it sounds like you are using the validation elements of the table and
not in the form. I would recommend *not* using the table validation and
instead use what I posted in the BeforeUpdate event of the TextBox control
for this field.

I use Validation Rule in form TextBox property.
Error message is in the Validation Text property.

Hi Igor,
You didn't say what event you are using to do the validation. Your code

[quoted text clipped - 11 lines]

 

[Tim Ferguson]

Igor, it sounds like you are using the validation elements of the
table and not in the form. I would recommend *not* using the table
validation and instead use what I posted in the BeforeUpdate event of
the TextBox control for this field.

Why do you say this? There are many ways of updating or filling fields in
tables: using a form, using a query, typing into the table datasheet, SQL
embedded in VBA, merging from Excel or Word... even CorelDraw has a VBA
module that can update a database. If the data in the table are to be
protected, then it has to be at the DBEngine level; and that means a
field-level or table-level ValidationRule.

You are half-right, of course. No self-respecting user likes to see a DB
error message about field contents, and no self-respecting developer
should let an error like that get sent to the engine. Therefore,
intelligent handling of e.g. the BeforeUpdate event is required too.

But don't imagine that a bit of code in one form is going to maintain the
integrity of the real data on its own.

Best wishes

Tim F