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