CODEWITHSUNDEEP
ms-accessvalidation
GazB
GazB

Reputation: 3695

How do I add a validation rule or input mask that ensures the textbox input is exactly 11 characters long in Access 2007

I am trying to ensure a text box in MS Access 2007 matches an employer reference number so I have used the "Input Mask":

    >###\/??#####;;_

This works apart from it allows you to enter half the reference number (say the first 3 numbers only) so I wanted to use a validation rule to ensure that the length of the input is exactly 11 character (including the "/").

I have tried the validation rule:

    =Len([EmployerRefNum])=11

BUT this seems to always cause the validation to fail. I have even tried removing the Input Mask and JUST having the validation rule then typing 11 chracters and this still fails.

P.S. I really also need to to be 0 or 11 character to allow for them to leave it blank encase they don't have the employer reference number with them currently I suspect once I have the valid 11 characters code I could do an OR(,) with the same code and 0 instead of 11.

Upvotes: 1

Views: 6011

Answers (3)

Renaud Bompuis
Renaud Bompuis

Reputation: 16776

The issue could be that your EmployerRefNum is interpreted as a number instead of a string, so the Len() will not work.

Maybe you could try something like:

  Is Null Or Len(CStr(Nz([EmployerRefNum]))) = 11

That will pass if the field is left blank or if the length of the data in exactly 11.

As Philippe said, you may be better off solving this issue using a combobox or validating your input from code if you really want to use a textbox and catch all possible user errors.

Validation fields and masks are really not user-friendly and not that flexible: you often spend more time trying to craft a properly working validation and mask than it would take to do it in VBA.

Using code you also have the luxury of informing the user in a nicer way than the horrible and verbose dialog box that pops up when the validation fails.

For instance, you could highlight the EmployerRefNum texbox in red when it fails validation:

Private Function IsValidEmployerRefNum() As Boolean
    IsValidEmployerRefNum = IsNull(EmployerRefNum) _
                            Or Len(CStr(Nz(EmployerRefNum))) = 11
End Function

Private Sub EmployerRefNum_BeforeUpdate(Cancel As Integer)
    EmployerRefNum.BackColor = Iif(IsValidEmployerRefNum(), vbWhite, vbRed)
End Sub

Public Function IsAllValid() As Boolean
    IsAllValid = IsValidEmployerRefNum() _
                 And IsValidMyOtherField() _
                 And IsValidYetAnotherField()
End Function

You can do complex validation logic in the IsAllValid() function and use that to allow the data to be saved at the form-level or not:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = Not IsValid()
End Sub

There are many ways to do these things, this is not meant to be a good example, there are lots of ways to make this better, but this can get you started.

Having your validation rules in code also have the advantage that they are in the same place rather than be scattered in controls and forms all over the place.
Ideally, you should even centralise all your validation rules in modules or classes as they are part of your business logic.
Keeping them in one place makes maintaining your code much easier as Business rules always change over time.

Upvotes: 2

TheOtherTimDuncan
TheOtherTimDuncan

Reputation: 603

If the different elements are required, not optional, why not use this for the input mask:

000\/LL00000;;_

Upvotes: 2

Philippe Grondier
Philippe Grondier

Reputation: 11138

Why don't you use a combobox with the employee list? This is the only way to make sure that data entered refers to a valid employee reference ...

Upvotes: 1

Related Questions