Field properties

Database

1. Field properties

2. objectives Field Formats Input mask Validation Rule Validation text Default value

3. Format The Format property affects only how data is displayed. It doesn't affect how data is stored. You can use one of the predefined formats or you can create a custom format by using formatting symbols. The Format property uses different settings for different data types . You can use the format to customize the way numbers, dates, times, and texts are displayed or printed. Symbol Meaning (space) Display spaces as literal characters. "ABC" Display anything inside quotation marks as literal characters. ! Force left alignment instead of right alignment. * Fill available space with the next character. \ Display the next character as a literal character. You can also display literal characters by placing quotation marks around them. [ colour ] Display the formatted data in the colour specified between the brackets. Available colours : Black, Blue, Green, Cyan, Red, Magenta, Yellow, White.

4. Format Property - Number and Currency Data Types You can create custom number formats by using the following symbols: Symbol Description . (period) Decimal separator. Separators are set in the regional settings in Windows. , (comma) Thousand separator. Digit placeholder. Display a digit or 0. # Digit placeholder. Display a digit or nothing. $ Display the literal character "$". % Percentage. The value is multiplied by 100 and a percent sign is appended.

5. Format Property - Number and Currency Data Types -You can use the format to customize the way numbers, dates, times, and texts are displayed or printed. -Custom format number can have one to four section with ( ; ) as the list separator. Section Description First The format for positive numbers . Second The format for negative numbers . Third The format for zero values. Fourth The format for Null  (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data .

6. Format Property - Number and Currency Data Types Examples: $#,##0.00 £ #,##0.00[Blue] +0.00;-0.00;0.00;”Null” 0;(0);;”Unknown” 00.00[Blue];(-00.00)[Red];” Zero”;”Empty ”

7. Format Property - Text and Memo Data Types Symbol Description @ Text character (either a character or a space) is required. & Text character is not required. < Force all characters to lowercase. > Force all characters to uppercase. You can create custom text and memo formats by using the following symbols.

8. Format Property - Text and Memo Data Types Section Description First Format for fields with text. Second Format for fields with zero-length strings  (zero-length string: A string that contains no characters . Custom formats for Text and Memo fields can have up to two sections. Each section contains the format specification for different data in a field.

9. Format Property - Text and Memo Data Types Examples: @;”None” >;@[Blue];”Unknown”[Red] @[Green] <@[Blue];”Null”[Red]

10. Format Property - Date/Time Data Type Symbol Description : (colon) Time separator  (separator: A character that separates units of text or numbers.) . Separators are set in the regional settings of Windows. / Date separator. c Same as the General Date predefined format. d Day of the month in one or two numeric digits, as needed (1 to 31). dd Day of the month in two numeric digits (01 to 31). ddd First three letters of the weekday (Sun to Sat). dddd Full name of the weekday (Sunday to Saturday). ddddd Same as the Short Date predefined format. dddddd Same as the Long Date predefined format. w Day of the week (1 to 7). ww Week of the year (1 to 53). m Month of the year in one or two numeric digits, as needed (1 to 12). mm Month of the year in two numeric digits (01 to 12). mmm First three letters of the month (Jan to Dec). mmmm Full name of the month (January to December). You can create custom date and time formats by using the following symbols.

11. Format Property - Date/Time Data Type q Date displayed as the quarter of the year (1 to 4). y Number of the day of the year (1 to 366). yy Last two digits of the year (01 to 99). yyyy Full year (0100 to 9999). h Hour in one or two digits, as needed (0 to 23). hh Hour in two digits (00 to 23). n Minute in one or two digits, as needed (0 to 59). nn Minute in two digits (00 to 59). s Second in one or two digits, as needed (0 to 59). ss Second in two digits (00 to 59). ttttt Same as the Long Time predefined format. AM/PM Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate. am/pm Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate. A/P Twelve-hour clock with the uppercase letter "A" or "P", as appropriate. a/p Twelve-hour clock with the lowercase letter "a" or "p", as appropriate. AMPM Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows

12. Setting Display ddd ", "mmm d", " yyyy Mon, Jun 2, 1997 mmmm dd", "yyyy June 02, 1997 "This is week number "ww This is week number 22 "Today is " dddd Today is Tuesday Example The following are examples of custom date/time format. Format Property - Date/Time Data Type

13. Input mask You can use the Input Mask property to make data entry easier and to control the values users can enter in a text box. Input masks are helpful for data-entry operations such as an input mask for a Phone Number field that shows you exactly how to enter a new number: (___) ___-____. When you've defined an input mask and set the Format property for the same field, the Format property takes precedence when the data is displayed. This means that even if you've saved an input mask, the input mask is ignored when data is formatted and displayed

14. Character Description 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  (separator: A character that separates units of text or numbers.) . (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). You can define a custom input mask by using the following characters.

15. Input mask Examples: Input mask Sample values [phone] [phone] [phone] [phone] ( ) 555-0248 (000) AAA-AAAA (206) 555-TELE #999 –20 2000 >L????L?000L0 GREENGR339M3 MAY R 452B7 >L0L 0L0 T2F 8M4 00000-9999 98115- 98115-3007 >L<?????????????? Maria Brendan SSN 000-00-0000 SSN 555-55-5555 >LL00000-0000 DB51392-0493

16. Validation Rule You can use the Validation Rule property to specify requirements for data entered into a record, field, or control. When data is entered that violates the Validation Rule setting, you can use the Validation Text property to specify the message to be displayed to the user. The maximum length for the Validation Rule property setting is 2048 characters. Microsoft Access automatically validates values based on a field's data type; for example, Microsoft Access doesn't allow text in a numeric field. You can set rules that are more specific by using the Validation Rule property. If you create a validation rule for a field, Microsoft Access doesn't normally allow a Null value to be stored in the field. If you want to allow a Null value, add "Is Null" to the validation rule, as in "<> 8 Or Is Null" and make sure the Required property is set to No.

17. Validation Rule and text examples Validation Rule property Validation Text property <> 0 Entry must be a nonzero value. > 1000 Or Is Null Entry must be blank or greater than 1000. Like "A????" Entry must be 5 characters and begin with the letter "A". >= #1/1/96# And <#1/1/97# Entry must be a date in 1996. >=18 And <=35 Entry must be between 18 and 35 including both numbers.

18. Default value Specifies a value that is automatically entered in a field when a new record is created. For example, in an Addresses table you can set the default value for the City field to Sulaimani. When users add a record to the table, they can either accept this value or enter the name of a different city . The Default Value property applies to all table fields except those fields with the data type of AutoNumber or OLE Object. The Default Value property is applied only when you add a new record. If you change the Default Value property, the change isn't automatically applied to existing records.