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  dBforums > PC based Database Applications > Microsoft Access > Restrict number of digits that can be entered in form field

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 25
Restrict number of digits that can be entered in form field

Hi again,

I know how to restrict the number of characters in a text field, but not the number of digits in a Number data type field. I was hoping to use an Input Mask, but that only works for text/date data types.

Any suggestions would be much appreciated!
G
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,786
You could try a validation rule like:

<1000

which should only allow 3 digits (unless you mean after the decimal point).
__________________
Paul
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 25
thanks Paul, but I realize now I should have refined my question to:

How do I restrict a number data field so it will only accept an exact number of numerical characters? (ie you must enter 7 digits)

Likewise, for a text field (where I can set the field size in the Properties menu) how can I make sure only text (no numerical characters) can be entered?

thanks very much,
G
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,786
I tend to work with a lot of "heads-down" data entry people (they aren't looking at the screen while entering). Thus I tend to use the before update event of the form to do all my validation (so it happens at the end, not while they're keying). In that event, if any control fails your test, add:

Cancel = True

and give the user a message box to tell them what went wrong. You can also set focus to the offending control and clear it if desired.
__________________
Paul
Reply With Quote
  #5 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Quote:
How do I restrict a number data field so it will only accept an exact number of numerical characters? (ie you must enter 7 digits)
Can the 7 digits have leading zeros?

A Validation Rule of Between 1000000 and 9999999 will force entries to 7 digits, but not with leading zeros.

If you want to allow leading zeros, then you have to re-think what it is you really want. Perhaps a Format of 0000000 will suffice?

Quote:
Likewise, for a text field (where I can set the field size in the Properties menu) how can I make sure only text (no numerical characters) can be entered?
Do some research on Input Mask.
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 25
Yes you're correct, the validation rule of Between 1000000 and 9999999 is problematic because it excludes leading zeros.

When I tried the Format of 0000000, the field now accepts any number of digits.

As for the text field that should only accept 2 upper case letters, I found validation code that excludes lower-case letters:
StrComp(UCase([Zone]),[Zone],0)=0

but unfortunately numbers can still be entered.

I tried this in the Input Mask: LL
but still no luck.

Any suggestions would be greatly appreciated.
G
Reply With Quote
  #7 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Quote:
Originally Posted by gbuckton
Yes you're correct, the validation rule of Between 1000000 and 9999999 is problematic because it excludes leading zeros.

When I tried the Format of 0000000, the field now accepts any number of digits.

As for the text field that should only accept 2 upper case letters, I found validation code that excludes lower-case letters:
StrComp(UCase([Zone]),[Zone],0)=0

but unfortunately numbers can still be entered.

I tried this in the Input Mask: LL
but still no luck.

Any suggestions would be greatly appreciated.
G
Ah well ok... so use a Format of 0000000 AND a Validation Rule of <= 9999999 and you should be set.

As for the text field, to restrict it to 2 U/Case letters, all you need is an Input Mask of >LL. That should work... it does for me.
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 25
The solution for the 2 U/Case letters worked great, however, the number field that should take only 7 digits including leading zeros is giving me trouble because if I now enter '12' and go to the next field it will automatically populates the field to say '0000012'. Instead, I would like it to give an error that 7 digits must be entered.

Hope that makes sense!
G
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,065
why would you need to enforce that, Like PKS I remain to be convinced that your data entry people will apprecaite the extra 5 key strokes

it also suggests that you are wanting to use a text box to capture numeric data

I suppose you could check the length len() of the string in the before update event and take appropriate action
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 25
There is not a lot of data entry going to be used, and I feel its important to maintain the highest quality of data entry by eliminating any automatic entry of zeros.

I have found a work around, by typing >0000000 in the Input Mask. Is there any way to include a customized error message? It just delivers the default error about not matching the Input Mask...

thx,
G
Reply With Quote
  #11 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Quote:
I feel its important to maintain the highest quality of data entry by eliminating any automatic entry of zeros.
Adjust your feelings? Seriously, I think you should reconsider that.
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On