Where's the Null?

Hi, folks. This is my first post in the forum. By way of introduction, I'm a clerk in a large bank who started creating Access databases about one and a half years ago as a solution to some of our tracking problems and paperwork overflow.

At the moment, I'm attempting to add a feature to one of my existing databases that will make certain numerical fields required depending on the content of certain other numerical fields. I was using VB to no effect so I backtracked some and tried manually changing the validation rule property on those controls in my form - to see if I could force a violation.

The default value of both the fields and controls are set to Null. My validation rule has been set to Is Not Null.

If I don't change the value of that cell, it would seem to me that I ought to get a validation rule violation, but it accepts the record just fine. I think this might be my real problem. For some reason, it isn't evaluating these fields as Null, even though I've made this my default.

Can anybody shed some light on this, or maybe offer me a work around?

Thanks in advance,
tera

Make sure the Required property is set to Yes. Also, you may try > 0 as the validation.

I'd rather not make my sub modify the actual table. I was hoping to work entirely with the controls in the form and keep my data source unchanged. I don't know about subsequent versions of Access, but 2002 doesn't have a 'required' control property that I can find, or I'd use that instead of validation.

Also, I did think to use >0 as an alternative to Is Not Null, but it still accepts the blank fields.

Also, I discovered something new yesterday afternoon. If I put the validation rule in the table, Is Not Null works! According to the Help file, the validation rules from both the form's control property and the table's field property are supposed to be evaluated, not one or the other. Obviously my syntax is correct (as demonstrated by what happens when I use it in the table). The only thing that I can think of is that the control Validation Rule property is buggy.

Hi, all. Just thought for the sake of closure I'd put up the work-around that I finally came to in my problem.

The lovely thing about Access is that there are two or three different paths you can take to any objective. In this case, I abandoned changing the properties of my controls and created a macro containing conditional actions of MsgBox and CancelEvent for each of the fields I wanted to evaluate. Works like a charm.

Thanks for posting a followup.

You're welcome.

Being new to the group, I wasn't sure about ettiquete, but I know the first thing I do when I'm having trouble is check thread archives and I wanted to make sure anyone who found this would see the solution I came to.

tera