By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

how to limit the length of characters of one text field

P: 26
I tried to limit one of my text field's that the length of input has to be 9 characters, I set the validation rule: =len(txtSSN)=9, and validation description: The field has to be 9 characters!
But the result is not my expected, whatever I inputted, it always give me: The field has to be 9 characters!

Anyone can help me on this issue?
Oct 22 '08 #1
Share this Question
Share on Google+
2 Replies


missinglinq
Expert 2.5K+
P: 3,532
Drop the leading equal mark.That type of syntax is used in assigning Control Sources, not Validation expressions. Simply use:

Len([txtSSN])=9

This could also be handled with validation code like:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSSN_BeforeUpdate(Cancel As Integer)
  2.   If Len(Me.txtSSN) <> 9 Then
  3.     MsgBox "The SSN Must Contain 9 Characters!"
  4.     Cancel = True
  5.   End If
  6. End Sub
The advantage of using the after update event in the code is that you can use a messagebox, which some people think gives a more pleasing appearance that the Access validation message box.

Linq ;0)>
Oct 22 '08 #2

P: 56
Hi Ling,
I have the same problem. However, in my case, I want it to be exactly 8 digits. I"ve used your suggested code but it dosen't work quite right. It always allow to add even though I only type in 2 or 3 digits.

Here is my code that I have in the "NOT IN LIST" function:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboID_NotInList(NewData As String, Response As Integer)
  2. Dim strMsg As String
  3. Response = acDataErrContinue
  4.  
  5.     If Len(Me.cboID) <> 8 Then    'if not =8, promt message
  6.     MsgBox "MRN Must be 8 digits", vbOKOnly
  7.     Me.cboID.SetFocus
  8.  
  9.     Else    ' if =8, allow to add new record
  10.         strMsg = "The Medical Record Number'" & NewData & "' you selected is not in the list. "
  11.         strMsg = strMsg & "Do you want to add it?"
  12.  
  13.         If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Patient?") = vbYes Then
  14.  
  15.             DoCmd.GoToRecord , , acNewRec
  16.             [txtMEDREC] = NewData
  17.             [txtMEDREC].Enabled = True
  18.             [txtPATFNM].Enabled = True
  19.             [txtPATLNM].Enabled = True
  20.             [txtPATSEX].Enabled = True
  21.             txtPATLNM.SetFocus
  22.             cmdAddNewPtAccount.Enabled = True
  23.         Else
  24.             Response = acDataErrContinue
  25.         End If
  26.  
  27.      End If
  28.  
  29. End Sub
I've tried to put the first If statement in the other functions such as AfterUpate, BeforeUdpate, none of them really work.
thanks for your help
bluemoon
Dec 29 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.