Results 1 to 11 of 11
  1. #1
    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

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    You could try a validation rule like:

    <1000

    which should only allow 3 digits (unless you mean after the decimal point).
    Paul

  3. #3
    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

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    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

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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?

    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

  6. #6
    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

  7. #7
    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

  8. #8
    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

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    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 on the Tiger 800 or the Norton

  10. #10
    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

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •