limiting characters in validation rule

[ditnog]

I'm trying to limit the number of characters entered in a field so that 5 or
more must be entered. I think I need to build an expression using "Len" but
can't get the syntax correct for the rest of it. I'm using Microsoft Access
2003.

Thanks for your help

 

[Al Campagna]

ditnog,
On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[ditnog]

Thank you - I tried to put that in the Expression Builder, but it's saying
"The expression you entered contains invalid syntax. You may have entered an
operand without an operator."

Also, when I look in the Validation Rule field under Field Properties, there
are square boxes where it appears I've done a hard return. Should the syntax
not have any hard returns?

ditnog,
On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to limit the number of characters entered in a field so that 5 or
more must be entered. I think I need to build an expression using "Len" but
can't get the syntax correct for the rest of it. I'm using Microsoft Access
2003.

Thanks for your help

 

[Graham R Seach]

Al, in the BeforeUpdate event, I think that should be
Len(Me.YourFieldName.Text).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

ditnog,
On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to limit the number of characters entered in a field so that 5
or
more must be entered. I think I need to build an expression using "Len"
but
can't get the syntax correct for the rest of it. I'm using Microsoft
Access
2003.

Thanks for your help

 

[missinglinq via AccessMonster.com]

There's a couple things wrong here. First, ditnog says "I'm trying to limit
the number of characters entered in a field so that 5 or more MUST be entered.
" The code

On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

is designed to assure that 5 or LESS characters are entered, not 5 or MORE.

Next ditnog says "I tried to put that in the Expression Builder, but it's
saying "The expression you entered contains invalid syntax." You cannot enter
a sub function in the Expression Builder, it has to go in the Code Builder!
Assuming that the original post was correct, and the intent is to assure that
5 or MORE characters are entered, in the BeforeUpdate event of the textbox
you need code like this


Private SubYourFieldName_BeforeUpdate(Cancel As Integer)
If Len(YourFieldName) < 5 Then
MsgBox ("Too short")
Cancel = True
YourFieldName.Undo
End If
End Sub

BTW, Len([YourFieldName]), Len([YourFieldName.Value]) or Len(Me.
YourFieldName.Text) would all work here. The first two are really the same
thing, as Value is the default, thus YourFieldName is the same as
YourFieldName.Value. YourFieldName.Text can only be used when YourFieldName
has the focus and so works here as well.

 

[ditnog]

Yes, correct - I am trying to make sure 5 or MORE characters are entered.
Although this explanation is a few steps above what I know how to do. I
played around and ended up putting Len([FieldName])>5 in the Expression
Builder and I think it's working. I'm not sure what BeforeUpdate or Code
Builder refers to, or how you even get to them.

Thanks for your help though - I was able to muddle through using the Len
argument. Just curious where someone would know how to build that argument?
Books? Anywhere in Microsoft Help?

 

[Al Campagna]

Graham,
Hmmm... not sure I understand...
I tested the code, just as I indicated, and it worked fine.
Is there something I'm missing here?
Al

Al, in the BeforeUpdate event, I think that should be Len(Me.YourFieldName.Text).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

ditnog,
On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to limit the number of characters entered in a field so that 5 or
more must be entered. I think I need to build an expression using "Len" but
can't get the syntax correct for the rest of it. I'm using Microsoft Access
2003.

Thanks for your help

 

[missinglinq via AccessMonster.com]

Al, as I indicated in my last post, Graham was mistaken in thinking you had
to use the Text Property

BTW, Len([YourFieldName]), Len([YourFieldName.Value]) or Len(Me.
YourFieldName.Text) would all work here. The first two are really the same
thing, as Value is the default, thus YourFieldName is the same as
YourFieldName.Value. YourFieldName.Text can only be used when YourFieldName
has the focus and so works here as well.

The only things wrong in your code were the lines

If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"

You apparently misread the original post, because ditnog wanted to make sure
that 5 or MORE characters WERE entered. The code should have read

If Len([YourFieldName]) < 5 Then
MsgBox "Too Short"

 

[RoyVidar]

Yes, correct - I am trying to make sure 5 or MORE characters are
entered. Although this explanation is a few steps above what I know
how to do. I played around and ended up putting Len([FieldName])>5
in the Expression Builder and I think it's working. I'm not sure
what BeforeUpdate or Code Builder refers to, or how you even get to
them.

Thanks for your help though - I was able to muddle through using the
Len argument. Just curious where someone would know how to build
that argument? Books? Anywhere in Microsoft Help?

There's a couple things wrong here. First, ditnog says "I'm trying
to limit the number of characters entered in a field so that 5 or
more MUST be entered. " The code

On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

is designed to assure that 5 or LESS characters are entered, not 5
or MORE.

Next ditnog says "I tried to put that in the Expression Builder, but
it's saying "The expression you entered contains invalid syntax."
You cannot enter a sub function in the Expression Builder, it has to
go in the Code Builder! Assuming that the original post was correct,
and the intent is to assure that 5 or MORE characters are entered,
in the BeforeUpdate event of the textbox you need code like this


Private SubYourFieldName_BeforeUpdate(Cancel As Integer)
If Len(YourFieldName) < 5 Then
MsgBox ("Too short")
Cancel = True
YourFieldName.Undo
End If
End Sub

BTW, Len([YourFieldName]), Len([YourFieldName.Value]) or Len(Me.
YourFieldName.Text) would all work here. The first two are really
the same thing, as Value is the default, thus YourFieldName is the
same as YourFieldName.Value. YourFieldName.Text can only be used
when YourFieldName has the focus and so works here as well.

Len([FieldName])>5 should work in the Validation Rule property of the
field, and has the advantage over using form events (the suggested
solutions) that

1 - you don't have to specify this rule every time/place you're
displaying this field to the user
2 - will also perform validation if someone updates the field from
other
sources, for instance from outside the app (query from another
app/script...?)

Remember to add an appropriate Validation Text.

 

[Graham R Seach]

Sorry Al, I couldn't be more wrong. I was thinking about the Change event.
Brain's not back in gear yet (long story).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,
Hmmm... not sure I understand...
I tested the code, just as I indicated, and it worked fine.
Is there something I'm missing here?
Al

Al, in the BeforeUpdate event, I think that should be
Len(Me.YourFieldName.Text).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

ditnog,
On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to limit the number of characters entered in a field so that
5 or
more must be entered. I think I need to build an expression using "Len"
but
can't get the syntax correct for the rest of it. I'm using Microsoft
Access
2003.

Thanks for your help

 

[ditnog]

Thanks so much - this is my first time posting and you've all been really
helpful. Thanks also for clarifying where the solution is best used, under
what conditions.

Yes, correct - I am trying to make sure 5 or MORE characters are
entered. Although this explanation is a few steps above what I know
how to do. I played around and ended up putting Len([FieldName])>5
in the Expression Builder and I think it's working. I'm not sure
what BeforeUpdate or Code Builder refers to, or how you even get to
them.

Thanks for your help though - I was able to muddle through using the
Len argument. Just curious where someone would know how to build
that argument? Books? Anywhere in Microsoft Help?

There's a couple things wrong here. First, ditnog says "I'm trying
to limit the number of characters entered in a field so that 5 or
more MUST be entered. " The code

On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

is designed to assure that 5 or LESS characters are entered, not 5
or MORE.

Next ditnog says "I tried to put that in the Expression Builder, but
it's saying "The expression you entered contains invalid syntax."
You cannot enter a sub function in the Expression Builder, it has to
go in the Code Builder! Assuming that the original post was correct,
and the intent is to assure that 5 or MORE characters are entered,
in the BeforeUpdate event of the textbox you need code like this


Private SubYourFieldName_BeforeUpdate(Cancel As Integer)
If Len(YourFieldName) < 5 Then
MsgBox ("Too short")
Cancel = True
YourFieldName.Undo
End If
End Sub

BTW, Len([YourFieldName]), Len([YourFieldName.Value]) or Len(Me.
YourFieldName.Text) would all work here. The first two are really
the same thing, as Value is the default, thus YourFieldName is the
same as YourFieldName.Value. YourFieldName.Text can only be used
when YourFieldName has the focus and so works here as well.

Len([FieldName])>5 should work in the Validation Rule property of the
field, and has the advantage over using form events (the suggested
solutions) that

1 - you don't have to specify this rule every time/place you're
displaying this field to the user
2 - will also perform validation if someone updates the field from
other
sources, for instance from outside the app (query from another
app/script...?)

Remember to add an appropriate Validation Text.

 

[ben]

Thank you - I tried to put that in the Expression Builder, but it's saying
"The expression you entered contains invalid syntax. You may have entered
an
operand without an operator."

Also, when I look in the Validation Rule field under Field Properties,
there
are square boxes where it appears I've done a hard return. Should the
syntax
not have any hard returns?

ditnog,
On BeforeUpdate of the field...
If Len([YourFieldName]) > 5 Then
MsgBox "Too Long"
Cancel = True
[YourFieldName].Undo
End if

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to limit the number of characters entered in a field so that
5 or
more must be entered. I think I need to build an expression using "Len"
but
can't get the syntax correct for the rest of it. I'm using Microsoft
Access
2003.

Thanks for your help