A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Data Validation Rule in Table Design



 
 
Thread Tools Display Modes
  #1  
Old January 5th, 2010, 12:24 AM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default Data Validation Rule in Table Design

Can you tell me how to write a data validation rule I can use in the table
design mode for a phone number field that will not allow blanks (eg Not null)
and allow user to write N/A if phone number is not known or available AND
allow user to type a phone number as (123) 123-3456 if phone number is
available.
Thanks.
  #2  
Old January 5th, 2010, 01:03 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Data Validation Rule in Table Design

Steve

One consideration ... if you want the field to hold either a number or a
text string (e.g., N/A), the underlying field needs to be text data type,
not numeric.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Steve Stad" wrote in message
...
Can you tell me how to write a data validation rule I can use in the table
design mode for a phone number field that will not allow blanks (eg Not
null)
and allow user to write N/A if phone number is not known or available AND
allow user to type a phone number as (123) 123-3456 if phone number is
available.
Thanks.



  #3  
Old January 5th, 2010, 03:04 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Data Validation Rule in Table Design

In table design, set the field's properties (lower pane) like this:
Validation Rule Not Null
Validation Text Enter something (even N/A) for phone.
Allow Zero Length No

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Steve Stad" wrote in message
...
Can you tell me how to write a data validation rule I can use in the table
design mode for a phone number field that will not allow blanks (eg Not
null)
and allow user to write N/A if phone number is not known or available AND
allow user to type a phone number as (123) 123-3456 if phone number is
available.
Thanks.


  #4  
Old January 5th, 2010, 06:24 AM posted to microsoft.public.access.tablesdbdesign
theDBguy[_2_]
external usenet poster
 
Posts: 29
Default Data Validation Rule in Table Design

Hi,

Not sure how you can create an Input Mask to allow both "N/A" and "(999)
999-9999". However, another option might be that:

Data Type: Text
Required: Yes
Allow Zero Length: No
Default Value: "N/A"

Just my 2 cents...


"Steve Stad" wrote:

Can you tell me how to write a data validation rule I can use in the table
design mode for a phone number field that will not allow blanks (eg Not null)
and allow user to write N/A if phone number is not known or available AND
allow user to type a phone number as (123) 123-3456 if phone number is
available.
Thanks.

  #5  
Old January 5th, 2010, 09:14 PM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default Data Validation Rule in Table Design

Thanks for reply DB guy,

You hit the nail on the head. i.e., the internal mask would not enable the
'n/a'.
I used 'is not null' validaion rule with a 'message' and no zero length
string.

"theDBguy" wrote:

Hi,

Not sure how you can create an Input Mask to allow both "N/A" and "(999)
999-9999". However, another option might be that:

Data Type: Text
Required: Yes
Allow Zero Length: No
Default Value: "N/A"

Just my 2 cents...


"Steve Stad" wrote:

Can you tell me how to write a data validation rule I can use in the table
design mode for a phone number field that will not allow blanks (eg Not null)
and allow user to write N/A if phone number is not known or available AND
allow user to type a phone number as (123) 123-3456 if phone number is
available.
Thanks.

  #6  
Old January 6th, 2010, 06:22 AM posted to microsoft.public.access.tablesdbdesign
theDBguy[_2_]
external usenet poster
 
Posts: 29
Default Data Validation Rule in Table Design

Hi Steve,

Glad to hear you got your solution; however, based on your description, I
thought that the phone number field is a required field. By just setting the
Validation Rule to Is Not Null, you might check if the user can skip that
field and not enter anything to see if the Validation Rule will still kick in.

Good luck with your project.


"Steve Stad" wrote:

Thanks for reply DB guy,

You hit the nail on the head. i.e., the internal mask would not enable the
'n/a'.
I used 'is not null' validaion rule with a 'message' and no zero length
string.

"theDBguy" wrote:

Hi,

Not sure how you can create an Input Mask to allow both "N/A" and "(999)
999-9999". However, another option might be that:

Data Type: Text
Required: Yes
Allow Zero Length: No
Default Value: "N/A"

Just my 2 cents...


"Steve Stad" wrote:

Can you tell me how to write a data validation rule I can use in the table
design mode for a phone number field that will not allow blanks (eg Not null)
and allow user to write N/A if phone number is not known or available AND
allow user to type a phone number as (123) 123-3456 if phone number is
available.
Thanks.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.