Prev: help designing query to get results
Next: Blendcode table and multiple plants
From: Steve Stad on 4 Jan 2010 18:24
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.
From: Jeff Boyce on 4 Jan 2010 19:03
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" <SteveStad(a)discussions.microsoft.com> wrote in message
news:BCBA33D3-BA1B-4113-9A76-A920D7E069FD(a)microsoft.com...
> 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.


From: Allen Browne on 4 Jan 2010 21:04
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" <SteveStad(a)discussions.microsoft.com> wrote in message
news:BCBA33D3-BA1B-4113-9A76-A920D7E069FD(a)microsoft.com...
> 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.

From: theDBguy on 5 Jan 2010 00:24
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.
From: Steve Stad on 5 Jan 2010 15:14
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.
 |  Next  |  Last
Pages: 1 2
Prev: help designing query to get results
Next: Blendcode table and multiple plants