Formatting Values for Output
When formatting output to be displayed in a report, the output is usually a combination of different data types. For example, there will be a standard text message that also displays the current value for a field. But this can cause problems because Crystal syntax is a type safe language. It doesn’t allow you to concatenate text with non-string data. To build an output string with a combination of data types, you have to convert everything to a string using the CStr() function. This method works with all data types. Since the CStr() function is designed primarily for outputting data on a report, it also lets you specify how to format the data.
The CStr() function is passed the value to format as the first argument and a formatting string as the second argument. The formatting string is used as a template that describes how the value should look after it gets converted. Table 6-6 shows the different formatting characters that can be used. Table 6-7 shows examples of how different values will look after being formatted.
Table 6-6. CStr() Formatting Characters
| Format | Description |
|---|---|
| # | Use with formatting numbers. Each instance of it allocates space for a single digit. If the number isn’t very large, the remaining part is filled with spaces. If the number is too large, the integer part is still fully displayed. Unused digits after the decimal are zero filled. |
| 0 | Use with formatting numbers. If the number isn’t large enough, it is padded with zeros. If the number is too large, the integer part will still be fully displayed. Unused digits after the decimal are zero filled. |
| , | Use with formatting numbers to designate the thousand separators. |
| . | Use with formatting numbers to designate the decimal separator. |
| d, M | Day and month as a number (without a leading zero). |
| dd, MM, yy | Day, month and year as a two digit number (with a leading zero when necessary). |
| ddd, MMM | Day and month as a three letter abbreviation. |
| dddd, MMMM, yyyy | Day, month and year fully spelled out. |
| h, m, s | Time portions as a number without a leading zero. |
| hh, mm, ss | Time portions as a two digit number (with a leading zero when necessary). |
| HH | Show hours using a 24 hour clock (military time). |
| T | Single character representation of AM/PM. |
| Tt | Two character representation of AM/PM. |
Table 6-7. CStr() Example Output
| # | CStr() | Output |
|---|---|---|
| 1 | CStr(1234, 2) | 1,234.00 |
| 2 | CStr(1234.567, 2) | 1,234.57 |
| 3 | CStr(1234.567, “#”) | 1234 |
| 4 | CStr(1234.567, “0”) | 1234 |
| 5 | CStr(1234, “0.##”) | 1234.00 |
| 6 | CStr(1234, “0.00”) | 1234.00 |
| 7 | CStr(1234.567, “#.##”) | 1234.57 |
| 8 | CStr(1234.567, “0.00”) | 1234.57 |
| 9 | CStr(1234.567, “#####”) | 1234 |
| 10 | CStr(1234.567, “00000”) | 01234 |
| 11 | CStr(#1/2/2003 04:05:06 am#, “d/M/yy h:m/s t”) | 2/1/03 4:5:6 A |
| 12 | CStr(#1/2/2003 04:05:06 pm#,”dd/MM/yyyy HH:mm:ss tt”) | 01/02/2003 16:05:06 AM |
| 13 | CStr(#1/2/2003 04:05:06 am#,”dd/MM/yyyy HH:mm:ss tt”) | 01/02/2003 04:05:06 AM |
| 14 | CStr(#3:20 PM#, “HH:mm”) | 15:20 |
Examples 1 and 2 are easy. The first argument is the number to format and the second argument is the number of decimals to display. If the number to format doesn’t have any decimals, then they are zero filled. Notice that in these examples as well as all the others, Crystal Reports rounds the decimals portion up.
With one exception, examples 3 through 10 are easy as well. The exception is that unlike the first two examples, the second argument is the format string. Using this format string lets you be very specific about how to format the number.
Stop for a moment and look at examples 1 and 5. Do you notice one thing different between them? The difference is that the output in example 5 doesn’t have a thousands separator. In both example 1 and example 5, no thousands separator is specified, but example 1 has it by default. This isn’t the case when you use a format string. The documentation says that the format string needs to use an optional argument to specify the thousands separator. But example 14 shows that Crystal syntax has a bug that keeps this from working.
Examples 5 and 6 show that if there aren’t enough decimals then both the “#” and the “0” will zero fill their positions.
Examples 9 and 10 show that if there aren’t enough digits to fill the whole number, then the “#” fills it with a space and the “0” fills it with a zero.
For examples 11 thru 14, be careful with capitalization. The compiler is case sensitive when formatting date strings. When entering a format string, refer back to Table 6-6 so that you get it right.
