Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-16-2019, 11:14 AM   #1
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Question Mixed Validation Error

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.

Juett is offline   Reply With Quote
Old 07-16-2019, 11:17 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,562
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Mixed Validation Error

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-16-2019, 11:33 AM   #3
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 797
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Mixed Validation Error

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

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 07-16-2019, 12:08 PM   #4
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Re: Mixed Validation Error

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.
Juett is offline   Reply With Quote
Old 07-16-2019, 12:16 PM   #5
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,562
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Mixed Validation Error

Quote:
Originally Posted by Juett View Post
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")
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-16-2019, 01:04 PM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 797
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Mixed Validation Error

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?
Quote:
you should always post what you tried - often eliminates such questions plus helps us get on the right track
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 07-16-2019, 11:15 PM   #7
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Re: Mixed Validation Error

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 boxs Validation rule property. I hope that clears up the issue.

Juett is offline   Reply With Quote
Old 07-17-2019, 12:00 PM   #8
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 797
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Mixed Validation Error

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?
Micron is offline   Reply With Quote
Old 07-17-2019, 12:24 PM   #9
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,562
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Mixed Validation Error

Quote:
Originally Posted by Juett View Post
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 boxs 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.
Attached Images
File Type: png validation.PNG (3.0 KB, 35 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-18-2019, 06:09 AM   #10
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Smile Re: Mixed Validation Error

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.
Juett is offline   Reply With Quote
Old 07-18-2019, 06:41 AM   #11
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,562
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Mixed Validation Error

Quote:
Originally Posted by Juett View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
mixed up forms losbol Forms 7 06-17-2019 01:57 PM
Validation Error ICTkirsten Forms 2 04-04-2015 09:05 AM
Validation error twitchie Tables 1 02-02-2007 10:50 AM
Mixed up Offices GTC General 5 06-22-2003 03:32 PM
[SOLVED] Validation error Rich Forms 1 03-25-2001 09:32 AM




All times are GMT -8. The time now is 12:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World