Mixed Validation Error (1 Viewer)

Juett

Registered User.
Local time
Today, 06:46
Joined
Jul 16, 2019
Messages
71
Hello guys,

I have a particular problem that I am struggling to figure out.

I would like to validate a form text field with two criteria, one number, and one text, for example: Between 10 And 15 Or “N/A”

The intended outcome is any number between 10 and 15 is allowed, and any number outside of this is not, and...the text N/A is also allowed, but nothing else outside of thi..

I cannot get this to work using the above validation or variations of it.

Does anyone have a solution?

Thanks very much.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:46
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum! How are you using the Validation? Are you referring to the Validation Rule property? Or, are you trying to use VBA to validate the user's input?
 

Micron

AWF VIP
Local time
Today, 02:46
Joined
Oct 20, 2018
Messages
3,476
you should always post what you tried - often eliminates such questions plus helps us get on the right track. If you posted

If Me.txtValue Between 10 And 15 Or = N/A Then

then the answer seems obvious (I'm sticking my neck out there) because then we could suggest
If Me.txtValue>=10 And Me.txtValue <=15 Or Me.txtValue = "N/A" Then

or
If (Me.txtValue>=10 And Me.txtValue <=15) Or Me.txtValue = "N/A" Then
 

Juett

Registered User.
Local time
Today, 06:46
Joined
Jul 16, 2019
Messages
71
Thanks very much for the replies and advice. I have been trying with the validation rule property. Using variations of my initial example. Is VBA the best / only way to achieve this? If so, thanks very much for the help.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:46
Joined
Oct 29, 2018
Messages
21,358
Thanks very much for the replies and advice. I have been trying with the validation rule property. Using variations of my initial example. Is VBA the best / only way to achieve this? If so, thanks very much for the help.
Well, since you only have a limited number of options anyway, maybe you can still use the Validation Rule with something like:
Code:
In("10","11","12","13","14","15","N/A")
 

Micron

AWF VIP
Local time
Today, 02:46
Joined
Oct 20, 2018
Messages
3,476
In post 1 you said it was a form field. In post 4 you say it's the validation rule property. These are not necessarily the same thing. The latter takes place at the table level where
>="10" And <="15" Or "N/A" should work on a text field but not a number field

and
>=10 And <=15 Or "N/A" will not work on a number field.

If the validation is at the table level but is not working at the form level, then perhaps the form control is not bound?
Time to 'fess up and say exactly what's going on.
Maybe you missed this?
you should always post what you tried - often eliminates such questions plus helps us get on the right track
 

Juett

Registered User.
Local time
Today, 06:46
Joined
Jul 16, 2019
Messages
71
Thanks for the advice. I was attempting the validation on a text box on a from that is bound to a table. I was using the text box’s Validation rule property. I hope that clears up the issue.
 

Micron

AWF VIP
Local time
Today, 02:46
Joined
Oct 20, 2018
Messages
3,476
Well, I played with this at the form and table level. Table generates errors with < > or = on a text field, which is what you'd need if you expect to put n/a in it. Form just ignores it. You will have to do this in code AFAIC, but I would not be mixing text and numbers, or relying on characters that look like numbers to be treated as text - especially with such comparisons. After all , when numbers are text, 100 is less than 20, so what good is that?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:46
Joined
Oct 29, 2018
Messages
21,358
Thanks for the advice. I was attempting the validation on a text box on a from that is bound to a table. I was using the text box’s Validation rule property. I hope that clears up the issue.
Hi. Have you tried my suggestion earlier in post #5? I just tried it out using the following settings, and it seemed to work as expected.
 

Attachments

  • validation.PNG
    validation.PNG
    3 KB · Views: 234

Juett

Registered User.
Local time
Today, 06:46
Joined
Jul 16, 2019
Messages
71
I tried both the VBA and form property recommendations, and both ideas worked in slightly different scenarios.

The Form property option...In("10","11","12","13","14","15","N/A")... worked exactly as it should for the exact values specified.

The Form property option...>="10" And <="15" Or "N/A"....worked with any number (including decimals) between the two figures, but only for numbers less than 100.

Variations of the VBA code below worked for any range of numbers (including decimals):

If Me![Textbox] = "N/A" Then
ElseIf Me![Textbox] <= 97 Or Me![Textbox] >= 105 Then MsgBox "Fail: Number should be between 98mm and 106mm."
End If

While I do agree that mixing number and text validation is certainly not preferable, workable solutions to my specific issue were found, so thank you very much for your assistance.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:46
Joined
Oct 29, 2018
Messages
21,358
While I do agree that mixing number and text validation is certainly not preferable, workable solutions to my specific issue were found, so thank you very much for your assistance.
Hi. Glad to hear we were able to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom