Introduction to Database Using MicroSoft Access 2013

1. Introduction to Database Using MicroSoft Access 2013 Part 5.1 November 16, 2014

2. Setting field properties

3. FIELD SIZE • Enter a value from 1 to 255. Text fields can range from 1 to 255 characters. For larger text fields, use the Memo data type. • Byte — Use for integers that range from 0 to 255. Storage requirement is 1 byte. • Integer — Use for integers that range from -32,768 to 32,767. Storage requirement is 2 bytes. • Long Integer — Use for integers that range from -2,147,483,648 to 2,147,483,647. Storage requirement is 4 bytes. • Single   Use for numeric floating point values that range from -3.4 x 1038 to 3.4 x 1038 and up to seven significant digits. Storage requirement is 4 bytes. • Double   Use for numeric floating point values that range from -1.797 x 10308 to 1.797 x 10308 and up to fifteen significant digits. Storage requirement is 8 bytes. • Replication ID   Use for storing a globally unique identifier required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format. • Decimal   Use for numeric values that range from -9.999... x 1027 to 9.999... x 1027. Storage requirement is 12 bytes.

4. FORMAT • Determines the way that the field appears when it is displayed or printed in datasheets or in forms or reports that are bound to the field.

5. INPUT MASK • Displays editing characters to guide data entry.

6. CAPTION • The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

7. DEFAULT VALUE • Automatically assigns the specified value to this field when a new record is added.

8. VALIDATION RULE • Supplies an expression that must be true whenever you add or change the value in this field.

9. VALIDATION TEXT • Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

10. REQUIRED • Requires that data be entered in the field.

11. SMART TAGS • Attaches a smart tag to the field.

12. TEXT ALIGN • Specifies the default alignment of text within a control.

13. UNICODE COMPRESSION • Compresses text that is stored in this field when less than 4,096 characters are stored.

14. ALLOW ZERO LENGTH • Allows entry (by setting to Yes) of a zero-length string ("") in a Hyperlink, Text, or Memo field.

15. APPEND ONLY • Determines whether to track field value changes.

16. indexed • Specifies whether the field has an index.

17. New values • Determines whether an AutoNumber field increments with each new value or uses random numbers.

18. Decimal places • Specifies the number of decimal places to use when displaying numbers.

19. Ime mode • Controls the conversion of characters in East Asian versions of Windows.

20. Ime sentence mode • Controls the conversion of characters in East Asian versions of Windows.

21. Show date picker • Specifies whether to show the Date Picker control.

22. Format for data fields

23. Format for number and currency fields

24. If you don't specify a custom format for numeric and currency values, Access displays numbers in the General Number format and currencies in the Currency format. • To create a custom format, use the formatting characters shown in the following table. You can also specify a color in which you want the number or currency.

29. Format for text and memo field

30. The Text and Memo data types do not accept predefined formats. The Text data type accepts only custom formats, the Memo data type accepts both custom and Rich Text formatting. • Typically, you apply custom formats to Text and Memo data types to make the table data easier to read. For example, if you use a Web form to collect credit card numbers, and you store those numbers without spaces, you can use a custom format to add the appropriate spaces to make the credit card numbers easier to read. • Custom formats for the Text and Memo data types allow only two format sections in a string. The first section of a format string controls the appearance of text, and the second section displays empty values or zero-length strings. If you don't specify a format, Access left aligns all text in datasheets.

34. Format for date and time field

35. If you don't specify a predefined or custom format, Access applies the General Date format — m/dd/yyyy h:nn:ss AM/PM. • Custom formats for the Date/Time fields can contain two sections — one for the date and another for time — and you separate the sections with a semicolon. For example, you can re-create the General Date format as follows: m/dd/yyyy;h:nn:ss.

40. Validation rule

41. Validation rule

42. What is validation rule • A validation rule limits or controls what users can enter in a table field or a control (such as a text box) on a form.

43. Syntax for creating a validation rule

46. Examples of validation rules

48. Validation rule reference • The following tables provide reference information for validation rules, including the syntax that the most common rules use, links to information about using wildcard characters in your rules, and examples that you can adapt for use with your data.