Need to limit field size between 14 and 30 characters

[justiwhi]

I have a field in a form for account numbers. The account numbers entered are alpha-numeric. I need to make sure the user entering the number enters an account number that is between 14 and 30 digits. Thanks for any help.

 

[Banana]

If you go to table design view, you can see a property row "Validation Rule"; read up on help files about Validation Rule and Len() function.

Alternatively, if you want to do it only for a control on this particular form and not everywhere in Access for this particular field, use BeforeUpdate() event of textbox with Len() function and on failure, use Cancel=True to cancel the update.

 

[justiwhi]

Thanks for the help Banana.
Maybe I should have informed of my skill level, which is low.
I tried the validation rule > 14 And < 30, but this limits it to a number between 14 and 30, instead of that many digits.
I also have tried writing this code, but have only gotten this far.

Private Sub sAcctNum_BeforeUpdate(Cancel As Integer)
If Len(sAcctNum) < 14 > 30 Then
MsgBox "Invalid Charge Number"
Exit Sub
End If
End Sub

I know I am missing a big chunk here, just don't know what that chunk is

 

[missinglinq]

You were close! This should do it:

Private Sub sAcctNum_BeforeUpdate(Cancel As Integer)
If Len(Me.sAcctNum) < 14 or Len(Me.sAcctNum) > 30 Then
   MsgBox "Invalid Charge Number"
   Cancel = True
End If
End Sub

 

[fibayne]

thanks for this solution I have just used it to set the number of digits to be entered in a field on a form to 5 and in conjuction with it checking for duplicates with the following

Private Sub ChqRecNo_BeforeUpdate(Cancel As Integer)
If DCount("ChqRecNo", "tbl_DataStore", "ChqRecNo = " & [Forms]![frm_DataForm_ChqRec]![ChqRecNo]) Then
MsgBox "That Cheque Rec Number already exists, please use the next consecutive number available "
Cancel = True
Me.Undo

End If

If Len(Me.ChqRecNo) < 5 Or Len(Me.ChqRecNo) > 5 Then
MsgBox "Invalid Cheque Rec Number"
Cancel = True
Me.Undo
End If
End Sub

cheers Fi

 

[missinglinq]

Glad we could help!