Link to home
Start Free TrialLog in
Avatar of decent1
decent1Flag for United States of America

asked on

Microsoft Access 2010 - Field or Table Properties to Prevent use of a "Comma" in a Text Data Field

I need to prevent the use of a comma in a text field [Name], because some agents use commas and some don't, which makes searches difficult.  Also leading-spaces and double-spaces between Last and First names is annoying, if there's a way to prevent such, it would be GREAT!

Also, use of > in the field results in all upper-case or capital letters in that table, once you exit the field, but if the characters were actually entered into the table in lower-case, then they appear in lower-case in other forms and reports.  I need the [Name] field to be all upper-case with no commas.  All data entry is facilitated by a form, so I'm hoping there is a way to force these parameters in either the table or the form.
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, why don't you have two separate fields for First and Last names.  This would be better than a single field.  Not only would it prevent some poor data entry problems, it would also make it easier when trying to sort or search by a particular field.
Nice piece of very neat code

I have to agree with @IrogSinta though about storing the names as separate fields - its much easier to combine firstname + lastname when you need to rather than trying to work with them as a single field.
I try to put as much data validation as I can into the data layer, so that any validation rules get enforced even if the form is bypassed.

The following field-level validation rule will present leading spaces, multiple consecutive spaces, and commas:

Not Like " *" And Not Like "*  *" And Not Like "*,*"

It will not enforce all upper case, but that can be controlled through other means.

I agree with Ron and KCTS, too, that combining the name into one column is a very bad idea.
Avatar of decent1

ASKER

User generated imageI have exhausted all efforts at getting a file from the big computer in the sky that feeds most of the entries in the db.  We still fat-finger about 20% of the entries.  The only way I can get the [NAME] is all CAPS, full name.  There are too many abiguous spaces in people's names to be able to take the name-string and derrive a LAST, FIRST, & MIDDLE.  I'm stuck with what I've got, unless we want to start fat-fingering 300 student names per week (students are "clients").  Ug.

First run of code in PressKey event is returning errors and did not prevent unwanted characters.  This is what was placed in the LName text box, associated with LName in tblStudents.

Private Sub LName_KeyPress(KeyAscii As Integer)
    'capitalize
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
   
    Select Case KeyAscii
        Case Asc("A") To Asc("Z")
        Case Asc("0") To Asc("9")
        Case Asc("'")
        Case Asc(" ")
            'prevent space at the beginning or double spaces
            If LName.Text = "" Or Right(LName.Text, 1) = " " Then KeyAscii = 0
        Case 8
            'backspace
        Case Else
            KeyAscii = 0
    End Select
   
End Sub
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of decent1

ASKER

matthewspatrick,

I like using the field's Data Property, "Validation Rule."  It also allows me to use the "Validation Text" that displays when the user violates the rule, in this case it states, "Do not use any punctuation or leading spaces!"

What to do about forcing "Upper-case," besides use of the > feature?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Another approach is to allow nonalphanumeric characters in names (after all, there are names such as O'Brien), and when you need to do a search or some other action where it would cause a problem, strip them out with this function:

Public Function StripNonAlphaNumericChars(strText As String) As String
'Strips a variety of non-alphanumeric characters from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 7-Feb-2005

On Error GoTo ErrorHandler

   Dim strTestString As String
   Dim strBadChar As String
   Dim i As Integer
   Dim strStripChars As String

   strStripChars = " `~!@#$%^&*()-_=+[{]};:',<.>/?" & Chr$(34) & Chr$(13) & Chr$(10)
   strTestString = strText

   For i = 1 To Len(strStripChars)
      strBadChar = Mid(strStripChars, i, 1)
      strTestString = Replace(strTestString, strBadChar, vbNullString)
   Next

   StripNonAlphaNumericChars = strTestString
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in StripNonAlphaNumericChars procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of decent1

ASKER

Excellent solution to control manual, keyboard data-entry.  Completely exceeds expectations!  Thanks!!

================
I would greatly appreciate help on
https://www.experts-exchange.com/questions/27930220/Microsoft-Access-2010-Default-Value-in-a-Form-Field-to-Populate-a-Table-Field.html