Required field Validation Rule

Hi all,

A2003 SP2:

     In my continuing quest for an easier way to do simple field validation I started looking at the "Validation Rule" property of fields while in table design. I just can't seem to get it to pop my "Validation Text" if the field's "Required" property = Yes    ...   no matter what I put in the validation Rule!!!

Not Is Null
Is Not Null

My current test is on a Text field.

I don't want form code because Form_BeforeUpdate does not catch Required fields before Form_Error barfs it up and I already wrote the code to handle this but it is a royal PITA and if I can get the field validation to work in the table then I bet I can use the same for the Validation Rule for the control on a bound form.

LVL 39
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
when i set the Required property to YES

the Validation Text did not appear,
the Access default message appeared.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>My current test is on a Text field.
I thought that the absense of a value in a text field would be an empty string "" and not a NULL.
Rey Obrero (Capricorn1)Commented:
did you set Allow Zero Length to NO
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

stevbeAuthor Commented:
so what expression are you suggesting that I use for my rule?

I just found this in 2007 help online and my guess (cobined with my tests) is that is is true for 2003 also

IS NOT NULL Forces users to enter values in the field. This is the same as setting the Required field property to Yes. However, when you enable the Required property and a user fails to enter a value, Access displays a somewhat unfriendly error message. Typically, your database is easier to use if you use IS NOT NULL and enter a friendly message in the Validation Text property.
stevbeAuthor Commented:
<did you set Allow Zero Length to NO>
stevbeAuthor Commented:
So ... if I turn Required off and then set the Validation Rule to Is Not Null, put in a decent message for Validation Text, I can then apply complete logic (using custom functions) in the control's & form's BeforeUpdate?

I guess I am gonna have to start thinking about how I develop apps a bit differently as I typically do all of the validation messaging in the FE.

stevbeAuthor Commented:
this is turning further into a pile of #$%^&*()_

so what rule is used to make sure they entered a number in a Long filed (instead of say the letter 'a') ... or even a number that is too large for the data type specified ... grrrr... does not exist :-(

so there is no way except capturing form_error event in a bound form to over-ride the default bogus Access messages !!!

Please ... someone tell me I am wrong :-)

hey ... where are all of the unbound form champions ... they could tell me I can avoid all this :-)
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Not sure if this helps, but...

The @ character works in the Format property, but not in the Input Mask or Validation Rule property.
incrediblejohnConnect With a Mentor Commented:
try this:
Len(yourfield & "") <> 0

What it does is turns any nulls into emtpy strings so it covers all options
stevbeAuthor Commented:
jim: @ format did not help
john: Len(yourfield & "") <> 0 did not help

I am now looking at calling the BeforeUpdate for the ActiveControl in the Form_Error event? It would be nice if I could use the CallByName here also ...

I'll leave this open for a bit longer to see if there any other suggestions.

Rey Obrero (Capricorn1)Commented:
tried john suggestion in a *bound* textbox
setting in the table for field Test

Validation Rule Len([Test] & "")<>0
Validation Text You have to Enter a value
Required No
Allow Zero Length No

i got the message  "You have to Enter a value"
when i tabbed out of the textbox
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
Hi (late to the party again huh :-S)

Have your interests shifted now Steve?  From doing this all in the tables (always gonna be something of a struggle with a Jet BE) to more friendly FE messages - without nasty default field ones popping up?
stevbeAuthor Commented:
I would like to know if there is a way to do it in tables but if not ... I should open a new Q to dole out some more points.

The more I look into this there does not seem to be a way to capture bad data types in field validation so I need to go back to using Form_Error ... so I might as well capture the .Required error there also ... I like to keep stuff together.

stevbeAuthor Commented:
thanks cap ... that verifies my findings :-)
Leigh PurvisDatabase DeveloperCommented:
Are we not wanting a bit more of a global solution now - than worrying about the Required/Is Null issue alone Steve?
stevbeAuthor Commented:
required(3314) and data types(2113) are the biggest headache.

question hopping ... CallByName?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.