Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Question Restricting characters in a field

    Hi, At work we use an Access 2007 database where one of the fields in a table is a policy number. I want to restrict users to use 9 to 12 mix of alpha and numeric characters but ensure that the string does not contain any spaces or dashes (this field is used elsewhere as a lookup and these characters cause the lookup to fail). How do I do this? The only format I am using currently is > to force any alpha characters into upper case

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maria,

    Add the following code to your Policy Number field:
    Code:
    Private Sub PrjNo_BeforeUpdate(Cancel As Integer)
    
       If Len(Me.PrjNo) < 9 Or Len(Me.PrjNo) > 12 Then
    
          MsgBox "Project Number must be between 9 and 12 characters in lenght." & _
                 vbCrLf & vbCrLf & "Please Correct...", vbCritical + vbOKOnly, _
                 "Error: Invalid Project Number Length"
          Cancel = True
          
       End If
        If InStr(Me.PrjNo, " ") > 0 Or InStr(Me.PrjNo, "-") Then
    
          MsgBox "Project Number must not contain blanks or dashes." & _
                 vbCrLf & vbCrLf & "Please Correct...", vbCritical + vbOKOnly, _
                 "Error: Invalid characters in Project Number"
          Cancel = True
          
       End If
         
    End Sub
    Note: You'll have to change the 5 references to PrjNo to the actual Field Name you use for your field.

    HTH
    Last edited by RetiredGeek; 2014-09-10 at 07:25.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    Azar (2014-09-19),simmo7 (2014-09-16)

Posting Permissions

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