Formatting Symbols Used in Reports and Queries


When working with Reports and Queries, you may have come across special symbols used to format data associated with the date and time fields. Besides the date and time fields, there is another set of special symbols used to format data in other numeric fields. For example, by changing the symbols used in the QRG (frequency) field, the data shown in the Logbook View will be displayed, per your definition.

Formatting Symbols Used in a Query also applies to the Report Wizard

To explore the use of the formatting symbols in greater detail, this section uses the same example described in the "Logbook View - Using Queries" section. If you need to review the screen images shown in that section click here.

If you have created a logbook in SWISSLOG and you would like to experiment with one of sample queries, follow the three steps listed below:

Changing the Loaded Query

There are a number of changes that can be made to a Query, which will affect the appearance of your log. However, this section is limited to describing the symbols used to control the format of data in numeric fields such as Time and Date, QRG (frequency), and BandID note, BandID is not the same as the Band field the Band field is not a numeric field.

The lower portion of the Query Editor window is a workspace or a table of cells defining which columns are displayed in the View Logbook. In the above screen image, the QSODate field was selected which activated the Value Editor menu.

Changing the Format of the QSODate Field for the Logbook View or the Report Wizard

First select a field, double-click on the field and the Value Editor will appear. Verify that the Value Editor lists the field you want to edit a common mistake is accidentally selecting another field.

Hint: You can also right-click on a field and select Edit Properties from the pop-up menu to open the Value Editor.

In the example above, you can see the Value Editor lists several properties specific to the QSODate field toward the bottom of the list, there are five properties defining how the QSODate field is displayed in the log. With the exception of the format property, each property offers a scrolling list of the possible values.

The QSODate Format property is somewhat different as it does not offer choices from a scrolling list. You can define the date format you prefer to use, by entering standard date abbreviations used by many programs and operating systems. Formatting symbols may be written in upper case or lower case letters both produce the same result. A few of the common time and date formats are shown in the table below:

Symbol Example Description
hh:nn 23:59 Hours and minutes using 24 hour clock
h:nn am/pm 11:59 pm Hours and minutes using 12 hour clock with am-pm indicator.
mmm d, yyyy Jan 1, 2004
mm/dd/yyyy 01/01/2004 Day and month might be ambiguous, depending on country.
dd mmm yy 01 Jan 04 From the perspective of readability, the dd mmm yy format is preferred. The day and month are not ambiguous and the symbol groups do not require punctuation although a single space between the letter groups enhances readability further.

Hint: The format properties used for the following fields; QSODate, Day, Month, and Year, only affect the corresponding values displayed in the Logbook View.

When using the date in a search value, the date must be entered as defined by the Windows operating system. If an error message appears, or if your search results in a empty logbook, verify that the date format used in the search is formatted correctly. An easy way to verify the correct format is to click on the arrow in the Date column heading and select (Custom) from the drop-down list. When the Custom AutoFilter appears, click on the arrow beside either of the empty boxes on the right side. When the calendar appears, click on Today the date shown will be in the correct Windows format click cancel to exit.

The table below is a complete listing of symbols that can be used to format the Date and Time fields. These symbols may be used in Queries and Reports.

Symbol Description
c Displays the date using the format given by the ShortDateFormat system setting, followed by the time using the format given by the LongTimeFormat system setting. The time is not displayed if the fractional part of the DateTime value is zero.
d Displays the day as a number without a leading zero (1-31).
dd Displays the day as a number with a leading zero (01-31).
ddd Displays the day as an abbreviation (Sun-Sat) using the strings given by the ShortDayNames system setting.
dddd Displays the day as a full name (Sunday-Saturday) using the strings given by the LongDayNames system setting.
ddddd Displays the date using the format given by the ShortDateFormat system setting.
dddddd Displays the date using the format given by the LongDateFormat system setting.
m Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mm Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mmm Displays the month as an abbreviation (Jan-Dec) using the strings given by the ShortMonthNames system setting.
mmmm Displays the month as a full name (January-December) using the strings given by the LongMonthNames system setting.
yy Displays the year as a two-digit number (00-99).
yyyy Displays the year as a four-digit number (0000-9999).
h Displays the hour without a leading zero (0-23).
hh Displays the hour with a leading zero (00-23).
n Displays the minute without a leading zero (0-59).
nn Displays the minute with a leading zero (00-59).
s Displays the second without a leading zero (0-59).
ss Displays the second with a leading zero (00-59).
t Displays the time using the format given by the ShortTimeFormat system setting.
tt Displays the time using the format given by the LongTimeFormat system setting.
am/pm Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
a/p Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
ampm Uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString system setting for any hour before noon, and the contents of the TimePMString system setting for any hour after noon.
/ Displays the date separator character given by the DateSeparator system setting.
: Displays the time separator character given by the TimeSeparator system setting.
'xx'/"xx" Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting.


Changing the Format of Numeric Field Values for the Logbook View or the Report Wizard

Numeric fields such as Frequency are formatted slightly differently than the fields related to date and time. While the concept is basically the same the symbols and their formatting masks are specific to numeric data. Below is an example of the frequency using a coma.

Note: BandID is also numeric field BandID is not the same as the Band field the Band field is not a numeric field.

Symbol Example Description
##,### 14,195 Frequency using a comma for the thousand separator.

Other valid formatting masks include the following:

Symbol Description
0 Digit placeholder. If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the output string. Otherwise, a '0' is stored in that position in the output string.
# The # character permits a numeric character or a plus or minus sign in this position, but does not require it.
. Decimal point. The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored. The actual character used as a the decimal separator in the output string is determined by the DecimalSeparator system setting. The default value of DecimalSeparator is specified in the Number Format of the International section in the Windows Control Panel.
, Thousand separator. If the format string contains one or more ',' characters, the output will have thousand separators inserted between each group of three digits to the left of the decimal point. The placement and number of ',' characters in the format string does not affect the output, except to indicate that thousand separators are wanted. The actual character used as a the thousand separator in the output is determined by the ThousandSeparatorsystem setting. The default value of ThousandSeparator is specified in the Number Format of the International section in the Windows Control Panel.
; Separates sections for positive, negative, and zero numbers in the format string.
E+ Scientific notation. If any of the strings 'E+', 'E-', 'e+', or 'e-' are contained in the format string, the number is formatted using scientific notation. A group of up to four '0' characters can immediately follow the 'E+', 'E-', 'e+', or 'e-' to determine the minimum number of digits in the exponent. The 'E+' and 'e+' formats cause a plus sign to be output for positive exponents and a minus sign to be output for negative exponents. The 'E-' and 'e-' formats output a sign character only for negative exponents.
'xx'/"xx" Characters enclosed in single or double quotes are output as-is, and do not affect formatting.

Top


Copyright 2004 SWISSLOG
Last modified: 01 Jan 2005