Record validation

[mistercormell]

Hi,

I have a table of associates, that have two important details stored about them: CRB Number and CRB Issue Date. Not all of the people need to have a CRB check (so won't need to store either the number or the date), however some do.

What I want Access to do, is to validate at record level- so that Either both those fields are Null or both the fields have data stored in them. e.g. you can't have data stored in one, but not the other.

I'm very new to Access and am doing it as part of a school project and our teacher is useless so I was wondering if anyone could give me a hand.

I've tried:

"[CRB Number] AND [CRB Issue Date] Is Not Null OR [CRB Number] AND [CRB Issue Date] = Null"

but to no avail.

Thanks

 

[Dennisk]

this cannot be done at the table level, either an entry is required or not.
However you can use the forms before Update event to test for these controls being Null or not.

 

[mistercormell]

How would you go about doing that in the forms then?

 

[Dennisk]

put this code in the forms Before_Update event

IF isnull([CRB Number]) AND isnull([CRB Check]) then
msgbox "your message here"
Cancel=true
endif

 

[mistercormell]

I had a long journey this afternoon and hadn't seen your answer at that point so I worked it out for myself!

Instead, I used a macro and attached it to the Before Update event of the form. I used a condition that showed the two incorrect options (number and no date, and date with no number):

[CRB] is Null AND [CRB Issue] is not Null OR [CRB] is not null AND [CRB Issue] is Null

This then openend a msgbox, and also the CancelEvent action.

It took me ages to work it out myself but go there!

 

[RoyVidar]

If you want the Jet engine to do the validation, you can create a record level validation rule for this. In design view of the table, right click and select properties, then in the validation rule property

([CRB] is Null AND [CRB Issue] is not Null) OR ([CRB] is not null AND [CRB Issue] is Null)

There are even more methods, but they aren't directly supported through the Access interface.

 

[mistercormell]

so you can use, the same line of code (except in parenthesis) for the form events in the table properties- that way they do the validation for you?

 

[RoyVidar]

The way I set up the validation, either

([CRB] is Null AND [CRB Issue] is not Null)

OR

([CRB] is not null AND [CRB Issue] is Null)

must be true. One of the fields must contain a value, the other field must be Null, which now after reading the initial post, is entirely wrong It should probably be

([CRB] Is Null AND [CRB Issue] Is Null) OR ([CRB] Is Null AND [CRB Issue] Is Null)

The parenthesis are there to group the logical expressions, and I think you would need them also when doing validation on the form.

 

[mistercormell]

Yes that's right, it dpeends on how you want to go about it: the final solution you gave was what I tried to do originally- which was to set the allowable data, but since I had no success with that I decided to set the validation up so that it flagged up when incorrect data was entered (ie. when one field was entered and not the other)

*It's amazing how much you learn yourself from several hour's messing around!* EDIT

 

[RoyVidar]

And it's amazing how much errors I can put into one single line

([CRB] Is Null AND [CRB Issue] Is Null) OR ([CRB] Is Not Null AND [CRB Issue] Is Not Null)