Validation Rules with Multiple Criterias

[victorsales]

Hi Folks,

I've been trying to get a field to behave but for some reason I must have done something wrong.

For this specific field people should only use numbers between 10,000 and 19,999 or between 80,000 and 89,999.

In the validation rule option I input:

Or Between 10000 And 19999 Or Between 80000 And 89999

I've also tried

>=10000 And <= 19999 Or >= 80000 And <=89999.

But for some reason MS Access accepts the first section of the validation but not the second part.

Can someone help me with the code?

Also: I've read that the validation rule is better managed at the Form level on the "Before Update" field, I'm currently managing it directly on the table in the field property. What should I use?

Thanks,
Victor

 

[pr2-eugin]

Victor I am quiet lost.. Are you saying you want to do this in Table level but not in Form level?

 

[RainLover]

I've read that the validation rule is better managed at the Form level on the "Before Update" field, I'm currently managing it directly on the table in the field property. What should I use?

I would do this in the form. The advice you were given is correct.

 

[victorsales]

Sorry if I wasn't clear enough.

I just need the code to work really.

It doesn't matter if it's managed at table level or form level.

I was just wondering why some people suggested to input the rule in one place rather than another.

But again I just need to get the validation rule right.

Thanks pr2-eugin

 

[RainLover]

If you validate at the Table level it is either right or wrong.
At a Form level you can introduce Message Boxes to explain why the value was not correct. There are many other things you may wish to do in different situations which you can do at the Form Level but not at the Table level.
Note, it must be in the Before Update not the After Update event.

Having answered that do you need help with the code or are you right to go now.

 

[pr2-eugin]

How about..

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not ((Me.theControlName Between 10000 And 19999) Or (Me.theControlName Between 80000 And 89999)) Then
        MsgBox "Sorry invalid entry try again", vbCritical,
        Cancel = True
    End If
End Sub

 

[victorsales]

Hi Paul,

Thank you for the VBA code. It's working perfectly!

Could you just please let me know how would you manage this on the Validation Rule field?

It seems to me that the code I entered below is not working. Is there anything wrong with the expression?

Or Between 10000 And 19999 Or Between 80000 And 89999

Thanks again.

 

[victorsales]

I also would like to thank you RainLover! Any help with the code would be appreciate!

 

[CazB]

do you actually need the first Or?

 

[victorsales]

Hi CazB,

the expressions listed above are the expressions I tried and didn't work.
I just don't know how to build the expression in order to take into account both ranges at the same time.

Thanks

 

[CazB]

yes, what I meant was: you just need to put

Between 10000 And 19999 Or Between 80000 And 89999

as the validation rule - in other words, miss out the first 'Or'

 

[RainLover]

Private Sub theControlName_BeforeUpdate(Cancel As Integer)
    If Me.theControlName >= 10000 And Me.theControlName <= 19999 Then
        If Me.theControlName >= 80000 And Me.theControlName <= 89999 Then
            Else
                MsgBox "Sorry invalid entry try again", vbCritical
                Cancel = True
        End If
    End If
End Sub

 

[RainLover]

I think that "Between" does not work.

Can anyone test to see if they can get it to work.

 

[victorsales]

Just tested it and it actually works only for the first "Between 10,000 and 19,999" if I try to input 81,000 I just get an error: "The value you have entered isn't valid for this field"

 

[PeterF]

Between does not work wit or as victorsales discovered, there's no reference for between in the help.

This works with an or.

(>10000 And <19999) Or (>80000 And <89999)

 

[CazB]

that's strange - I set up a new field in a table in one of my databases, set it as Number and put in the validation rule on the field as
Between 10000 And 19999 Or Between 80000 And 89999
and it works for me?
I'm using access 2007

 

[RainLover]

Just tested it and it actually works only for the first "Between 10,000 and 19,999" if I try to input 81,000 I just get an error: "The value you have entered isn't valid for this field"

The Logic is not correct. I think the "or" on its own is the problem.

Try what I posted. I know it works because I tested it.

 

[victorsales]

Yes, I got it to work now using PeterF's suggestion!
Thank you all guys for your time!!!

There was also another issue with my field. I had it set up as Integer so the max you can input is between -32,767 and 32,767 after I changed it into Long Integer everything started working!

 

[victorsales]

that's strange - I set up a new field in a table in one of my databases, set it as Number and put in the validation rule on the field as
Between 10000 And 19999 Or Between 80000 And 89999
and it works for me?
I'm using access 2007

CazB,
I tried it now again and the expression Between 10000 And 19999 Or Between 80000 And 89999 actually works. The problem in my case was that the field was set to Integer! After I have changed it into Long Integer it started working. Thanks for your time!

 

[victorsales]

Hey guys! Thank you a lot!
It's just that you have to look in so many places before you get something to work....