Field validations

[moynul]

hi everyone, i am currently in the process in making a database for my a-levels coursework in ict, i am a begginner to microsoft access and i am really stuck at the momemt,

in my database i have to validate fields where some fields only accept alphabets and some only accept numbers and just cant figure out how to do it, i'v looked all over the internet but just cant find any help.

can someone please help me and tell me how to do this asap.

THANK YOU

[nico5038]

You can use an input mask for this.
Just check the helpfile for the available codes.

For numeric fields just use a numeric fieldtype.

Nic;o)

[missinglinq]

Are these fields that "only accept numbers" actually numerical fields? A numerical datatype, such as Integer or Long or Double, should only be used if you're actually going to use them as numbers, which is to say doing math with them. If it's just a field that needs to accept only numerical characters, such as an ID number or a Parts number, you should use a text datatype.

[ADezii]

hi everyone, i am currently in the process in making a database for my a-levels coursework in ict, i am a begginner to microsoft access and i am really stuck at the momemt,

in my database i have to validate fields where some fields only accept alphabets and some only accept numbers and just cant figure out how to do it, i'v looked all over the internet but just cant find any help.

can someone please help me and tell me how to do this asap.

THANK YOU

To expand on Nico5038's response, the following characters define an Input Mask:
Character - Description
0 - Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9 - Digit or space (entry not required, plus and minus signs not allowed).

# - Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).

L - Letter (A to Z, entry required).
? - Letter (A to Z, entry optional).
A - Letter or digit (entry required).
a - Letter or digit (entry optional).
& - Any character or a space (entry required).
C - Any character or a space (entry optional).

. , : ; - / Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).

< - Causes all characters to be converted to lowercase.
> - Causes all characters to be converted to uppercase.

! - Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.

\ - Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

Examples:
LLLLLL - 6 Letters [A-Z] are 'required'
AAAAAA - 6 Letters or Digits are 'required'

NOTE: Select the Input Mask Field for a Field in Table Design View on the bottom Pane and press F1

[missinglinq]

Like a lot of people I try to stay away from input masks as a rule; they can be messy and require extra work if users click into the field rather than tabbing into it. But input masks would do the job. The poster's statement

in my database i have to validate fields where some fields only accept alphabets and some only accept numbers

combined with the fact that this is a class project makes me wonder if the instructer specifically wants the fields to be validated, as an instructional gimmick.

If the field is defined in the table as numerical, Access itself will indicate that there's a problem if non-numerical data is entered. It pops up a rather inelegant error message stating that the data is inappropriate. The usual way of turning off Access' messages (DoCmd.SetWarnings False) doesn't work here.

If the field's datatype is actually Text and you simply want to insure that all the characters are numerical you can use the IsNumeric function:

Private Sub Numbers_BeforeUpdate(Cancel As Integer)
 
'This insures that all characters are Numbers
If Not IsNumeric(Me.Numbers.Value) Then
   MsgBox "All characters must be Numbers"
   Cancel = True
   Me.Numbers.SelStart = 0
   Me.Numbers.SelLength = Len(Me.Numbers.Value)
 End If
End Sub
 

Unlike Excel, there is no IsText function in Access. Since Access allows alpha and numeric characters in Text fields, you can't use the opposite logic with IsNumeric, you have to loop thru the field one character at a time and check whether or not each one is numeric.

 Private Sub Letters_BeforeUpdate(Cancel As Integer)
Dim I As Integer
Dim Hits As Integer
Hits = 0
'This insures that all characters are Letters
 
For I = 1 To Len(Me.Letters.Value)
 If IsNumeric(Mid(Me.Letters.Value, I, 1)) Then
   Hits = Hits + 1
 End If
Next I
 
 If Hits > 0 Then
   MsgBox "All characters must be Letters"
   Cancel = True
   Me.Letters.SelStart = 0
   Me.Letters.SelLength = Len(Me.Letters.Value)
 End If
End Sub