|
Changing a Field's Type
The field type specifies how you want data to be represented in a field. Monarch Classic supports field types of character, date/time, numeric and memo. Note: While there is only one format option for the character and memo field types, there are several formats available for date/time and numeric field types, so when using these field types, be sure to select the proper format option.
Steps:
-
Select Field List from the Table Design ribbon. The Field List window displays. This window displays the properties for all fields in the table, including the field types.
-
To change a field’s type, click on the appropriate Field Type cell to display a drop-down list box in the cell. Click the arrow to expand the list, then select a new type. For fields that were imported from an external database, the list of field types is limited to those that are compatible with the source data. For fields extracted from a report file, all types are available. If you need to restore the previous field type, press Escape, or click the window’s button.
Note: Changing field types can have subtle implications for the field’s data and for the entire Monarch Classic session, affecting calculated field, filter, and summary definitions and, in rare cases, causing the Monarch Classic table to exceed its size limits. When you change a field’s type, Monarch Classic performs a series of tests to ensure that no ill effects will result, and will display an error if any of these tests fail. After clearing the error box, you may change the field type to correct the error or press Escape to restore the previous field type.
-
Repeat Step 2 to change the type for additional fields.
-
Click Accept to close the Field List window and apply the changes to the table.
Monarch Classic field types
Monarch Classic supports the following field types:
Character
You may assign the character field type to any field, including fields containing dates and numbers. Monarch Classic automatically assigns the character type to the following fields:
-
Fields containing letters.
-
Fields containing numbers beginning with a zero, such as "02134", which Monarch Classic interprets as a postal code rather than a number, and numbers containing more than 15 significant digits (the maximum allowed).
-
Fields containing numbers represented in scientific notation. To convert the resulting character strings to numeric values, you must use the Val function in a calculated field.
-
Multiple line fields. The data from each line of the field is trimmed [extra spaces are removed], then a single space is added to act as a delimiter and all lines are unwrapped to form a single line string. The string is truncated, if necessary, at 254 characters (the maximum length of a character field).
-
Imported fields whose type is character.
Assigning the Character Type to a Calculated Field
If you assign the character type to a calculated field that is the result of a numeric computation, you must apply the Str function in the calculated field expression to convert the value to a character string.
TOP OF PAGE
Date/Time
You can assign the date/time field type to any field whose values represent valid dates in the range January 01, 1601 - December 31, 2400.
Monarch Classic supports the following date/time formats:
|
Format Type |
Meaning |
|
Date/Time - General |
Displays date as "yyyymmdd" if there is a non-zero date part. Displays time as "hh:mm:ss" if there is a non-zero time part. If both the date and time parts are zero, this format shows "00:00:00" (i.e., midnight |
|
Date/Time - Short Date |
Shows the date using Windows Short Date setting (e.g., April 16, 2003 would be represented as "04/16/2003"). Does not show the time. |
|
Date/Time - Long Date |
Displays dates as specified by the Windows Long Date setting (April 16, 2003 would be represented as "Wednesday, April 16, 2003"). Does not show the time. |
|
Date/Time - Short Date & Time |
Shows the date using Windows Short Date setting (e.g., April 16, 2003 would be represented as "04/16/2003"). Displays time as "hh:mm:ss" if there is a non-zero time part. If both the date and time parts are zero, this format shows "00:00:00" (i.e., midnight). |
|
Date/Time - Long Date & Time |
Displays dates as specified by the Windows Long Date setting (April 16, 2003 would be represented as "Wednesday, April 16, 2003"). Displays time as "hh:mm:ss" if there is a non-zero time part. If both the date and time parts are zero, this format shows "00:00:00" (i.e., midnight |
|
Date/Time - Time |
Displays time as "hh:mm:ss" if there is a non-zero time part. Does not display the date. |
Date/time values are stored internally as "double" floating point values where the integer portion is a day number (i.e., the date) and the fraction portion is the time of day. An integer portion of zero corresponds to December 30, 1899. A fractional portion of zero corresponds to midnight. Thus the zero date/time is midnight on December 30, 1899.
Monarch Classic automatically assigns Date type to fields whose values it recognizes as dates. Monarch Classic uses its Date Format default setting to interpret the format of dates extracted from a report (see Date Format under Input options). Monarch Classic recognizes any date provided it appears in the report file as some variant of MDY, DMY or YMD. Field values that do not represent a valid date will display and export as blank.
If you specify MDY as the default date format, Monarch Classic will recognize dates extracted from the report file in any of the following formats:
|
3.08.00 |
3.08.2000 |
03.08.00 |
03.08.2000 |
Mar.08.00 |
March.08.2000 |
|
3 08 00 |
3 08 2000 |
03 08 00 |
03 08 2000 |
Mar 08 00 |
March 08 2000 |
|
030800 |
03082000 |
Mar0800 |
Mar082000 |
March0800 |
March082000 |
|
3-08-00 |
3-08-2000 |
03-08-00 |
03-08-2000 |
Mar-08-00 |
March-08-2000 |
|
3/08/00 |
3/08/2000 |
03/08/00 |
03/08/2000 |
Mar/08/00 |
March/08/2000 |
If you specify DMY, Monarch Classic recognizes these formats:
|
08.3.00 |
08.3.2000 |
08.03.00 |
08.03.2000 |
08.Mar.00 |
08.March.2000 |
|
08 3 00 |
08 3 2000 |
08 03 00 |
08 03 2000 |
08 Mar 00 |
08 March 2000 |
|
080300 |
08032000 |
08Mar00 |
08Mar2000 |
08March00 |
08March2000 |
|
08-3-00 |
08-3-2000 |
08-03-00 |
08-03-2000 |
08-Mar-00 |
08-March-2000 |
|
08/3/00 |
08/3/2000 |
08/03/00 |
08/03/2000 |
08/Mar/00 |
08/March/2000 |
If you specify YMD, Monarch Classic recognizes these formats:
|
00.3.08 |
2000.3.08 |
00.03.08 |
2000.03.08 |
00.Mar.08 |
2000.March.08 |
|
00 3 08 |
2000 3 08 |
00 03 08 |
2000 03 08 |
00 Mar 08 |
2000 March 08 |
|
000308 |
20000308 |
00Mar08 |
2000Mar08 |
00March08 |
2000March08 |
|
00-3-08 |
2000-3-08 |
00-03-08 |
2000-03-08 |
00-Mar-08 |
2000-March-08 |
|
00/3/08 |
2000/3/08 |
00/03/08 |
2000/03/08 |
00/Mar/08 |
2000/March/08 |
Interpreting Dates with 2 Digit Years
You must use the Date Range default setting to tell Monarch Classic how to interpret dates that have only two digits for the year, such as 08-Mar-15, which could refer to 1915 or 2015. See the "Date range for 2-digit years" option on the Input Options dialog.
TOP OF PAGE
Numeric
You may assign the numeric field type to any field whose values represent valid numbers.
Monarch Classic provides the following numeric styles:
-
General: Select this to represent numbers in their most compact form (e.g., 1234.56).
-
Thousands: Select Thousands to represent numbers as specified by the Windows Number settings (these settings usually specify a thousands separator, as in 1,234.56). The Windows Number settings may be modified using the Windows Control Panel Regional Settings applet.
-
Currency: Select Currency to represent numbers as specified by the Windows Currency settings (these settings specify how numeric values should be represented in the local currency). The windows Currency settings may be modified using the Windows Control Panel Regional Settings applet.
-
Percentage: Select Percentage to represent numbers as a percent of 100. Each number is multiplied by 100, then displayed with a trailing percent sign.
-
Time span: Select time span to display elapsed time (assumed to be in seconds) in HHHH:MM:SS.sss format. For example, in a time span field an elapsed time of 70 seconds (i.e., 1 minute and 10 seconds) would be displayed as "00:01:10". For more information see Using time span in a table or summary.
Monarch Classic automatically assigns numeric type to fields containing valid numbers. Valid numbers may contain up to 15 significant digits and an optional decimal point and negation sign. Any field value that does not represent a valid number is displayed and exported as blank.
Monarch Classic recognizes numbers that appear in the following formats:
-
Numbers with embedded commas, currency symbols and negation signs. Valid negation signs are parentheses, a leading or trailing minus sign (-), and the credit (CR) symbol. Valid positive numbers may include the plus sign (+) and the debit (DB or DR) symbols.
-
International numbers. Monarch Classic can recognize international numbers in the form 24.347.143,50 when you set the Decimal Character default to a comma. See Decimal Character under Input Options dialog.
Monarch Classic treats the following numbers as character strings and assigns character type rather than numeric:
-
Numbers beginning with a zero, such as "02134", which Monarch Classic interprets as a postal code rather than a number, and numbers containing more than 15 significant digits (the maximum allowed).
-
Numbers represented in scientific notation, such as 2.045E+03. To convert the resulting character strings to numeric values, you must use the Val function in a calculated field.
Assigning Numeric Type to a Calculated Field
If you change the field type of a calculated field to numeric, you may also need to adjust the field's decimal accuracy (see Changing a numeric field's decimal accuracy).
TOP OF PAGE
Memo
You may assign the memo field type to any field extracted from a report, including fields containing dates and numbers. You may also assign the memo field type to calculated fields. Memo fields are character-based; they can accept any string, including letters, numbers and punctuation. The length of a memo field is determined by the data in the field, with a maximum length of 64KB.
Memo fields cannot be used in sort or summary definitions. This field type is typically used to hold data extracted from a multiple line field.
When a multiple line field is extracted from a report, Monarch Classic initially assigns Character type to the field. When you change the field type to Memo, Monarch Classic displays only the first line of the field, but adds an ellipsis (...) to indicate that more data is available in the field. For more information on how Monarch Classic handles memo fields, see Changing a field's data length and Exporting the table to a file.
TOP OF PAGE