Validation Rules "NewBe"

[s23wins]

"Thanks for taking the time to help me in Advance"

I would like to Validate something like this.

FieldA >0 Or FieldB Is Not Null

In my mind this would return a validation error if
FieldB is Blank and FieldA is Greater than 0

Note: In FieldA the default data is 0

What I would like to happen is if a user updates the "Quantity" in FieldB with out Selecting the "Defect Type" In FieldA (error)

I just found the below code works for my table properties, but only for one DefectCode1 Field and matching DCodeQy Field.
([DefectCode1] Is Not Null) And ([DCode1Qy]>0)

Now my only problem is I dont know how to include all of my Fields under this one Table Property.

Eaxample:
([DefectCode1] Is Not Null) And ([DCode1Qy]>0)
([DefectCode2] Is Not Null) And ([DCode2Qy]>0)
([DefectCode3] Is Not Null) And ([DCode3Qy]>0)



Thanks
Again!!!!!!!**********

 

[stopher]

I just found the below code works for my table properties, but only for one DefectCode1 Field and matching DCodeQy Field.
([DefectCode1] Is Not Null) And ([DCode1Qy]>0)

Are you sure this is what you want? This means if nothing is entered at all in these two fields then the validation will fail. Even if you want this to be true, I suspect you don't necessarily want the same for the 2nd and 3rd defects.

If no entry is acceptable then the expression for one defect would be:

([DCode1Qy]<=0) OR ([DefectCode1] Is Not Null) And ([DCode1Qy]>0)

To apply this to all three, the expression would be:

(([DCode1Qy]<=0) Or ([DefectCode1] Is Not Null) And ([DCode1Qy]>0)) And (([DCode2Qy]<=0) Or ([DefectCode2] Is Not Null) And ([DCode2Qy]>0)) And (([DCode3Qy]<=0) Or ([DefectCode3] Is Not Null) And ([DCode3Qy]>0))

This expression can go in the table properties. In table design view, click View=>Properties and you will see the validation that you can apply across multiple fields.

Note that the user can still enter a defect code without entering a quantity. The validation will accept this (I think that's what you asked for).

Also, note I've overloaded the parenthesis. It's worth doing in order to ensure you don't get the wrong boolean result.

Btw, I suspect your table is not normalised. If you have repeating fields like this, they should go in a separate table. One of the benefits is your validation becomes simpler.

hth
Chris