Formatting Values for Output
When formatting output to be displayed in a report, the output is usually a combination of string data variables. As the last section stated, Basic syntax is a type safe language. As a result, you can’t concatenate strings with numbers or dates. To build an output string with a combination of data types, you have to convert everything to a string using the CStr() method. This method takes all data types and converts them to a string.
The CStr() function is usually passed the value to format as the first parameter and the formatting string as the second parameter. The formatting string is used as a template that describes how the value should look when it gets converted as a string. Table 9-6 shows the different formatting characters that can be used. Table 9-7 shows examples of how different values will look after being formatted.
Table 9-6. CStr() Formatting Characters
| Format | Description |
|---|---|
| # | Use with formatting numbers. If the number isn’t large enough, then spaces will be used instead. If the number is too large, then the integer part will still be fully displayed. Unused digits after the decimal are zero filled. |
| 0 | Use with formatting numbers. If the number isn’t large enough, then it will be padded with zeros. If the number is too large, then 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 9-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(1234.567, “#”, 2) | 1234 |
| 12 | CStr(1234.567, “#.##”, 2) | 1234.57 |
| 13 | CStr(1234.567, “#.###”, 2) | 1234.57 |
| 14 | CStr(1234.567, “#.##”, 2, “,”) | 1234.57 |
| 15 | CStr(1234.567, “#.##”, 2, “.”, “,”) | 1234,57 |
| 16 | CStr(1234.567, 2, “.”, “,”) | 1.234,57 |
| 17 | CStr(1234.567, “###,###.##”) | 1,234.57 |
| 17 | CStr(#1/2/2003 04:05:06 am”, | “d/M/yy H/m/s t”) |
| 1/2/03 4: 5: 6 A | 18 | CStr(#1/2/2003 04:05:06 am”, |
| “dd/MM/yyyy HH/mm/ss tt”) | 01/02/2003 04:05:06 AM | 19 |
| CStr(#1/2/2003 04:05:06 am”, | “dd/MM/yyyy hh/mm/ss tt”) | 01/02/2003 04:05:06 AM |
| 20 | CStr(#3:20 PM#, “HH:mm”) | 15:20 |
Table 9-7 shows examples of many different CStr() function calls and the associated output. On the surface, this looks very straightforward and everything matches what was stated in Table 9-6. However, we both know that in the world of software when you look below the surface things aren’t always what they seem. You’ll soon see that the simple CStr() function can get very complicated.
Examples 1 and 2 are easy. The first parameter is the number to format and the second parameter 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, the decimals are rounded up.
With one exception, examples 3 through 10 are easy as well. The exception is that unlike the first two examples, the second parameter 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 in when you use a format string. The documentation says that the format string needs to use an optional parameter to specify the thousands separator. But example 14 shows that Basic 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.
Examples 11 through 13 are where things really get interesting. These examples show that you can use the format string and also specify how many decimals to display. If you think about this for a minute, it may not make sense why you would do this. After all, if you were using a format string, you shouldn’t have to specify the number of decimals because it is already part of the format string. The only time you will do this is when you pass the optional parameters that specify the thousands separator and decimal character. Then you are forced to list all optional parameters and this means also specifying the number of decimals.
When you do specify the number of decimals, you should specify the same number as what your format string allows. If you specify a number different than what is in this string, then it uses the lesser of the two. Example 11 shows that the format string doesn’t allow any decimals and that is how it is displayed. Example 13 shows that the number of decimals is two, and the format string allows three decimals. Thus, two decimals are displayed.
Example 14 shows that you can have a third optional parameter that specifies the thousands separator. Unfortunately, the output shows you that it doesn’t have any effect. Example 15 tries to illustrate how to show a number using the European format of using a period for the thousands separator and a comma for the decimal. The output shows that the comma is now the decimal, which is correct. But again, no thousands separator is shown.
Example 16 shows that if you don’t use a format string then everything works out perfectly.
Does this mean that if you use the format string then you can’t have a thousands separator? No. It just means you have to do it manually. Example 17 shows a working example where everything is typed in. The proper characters are entered exactly where they belong in the string. If you use this method, be sure that you specify enough characters for the largest number that could be displayed.
The good news is that these problems only happen when you are formatting numbers for international display. By default, Basic syntax uses the computers international settings to determine how to format the number and you don’t have to specify the format string. If you must use a format string then you will have to do it all manually like example 17.
All these variations on how to use the format string and remember where the bugs are can be pretty confusing. Just remember that if your format string doesn’t act the way you think it should, then come back to this section for a quick refresher.
Unfortunately, the preformatted date strings that are in .NET (e.g. LongDate, ShortDateTime, etc.) are not in Crystal Reports. You have to write the entire format string manually. Although this certainly isn’t difficult, the .NET feature has me spoiled.
The dates that are illustrated in examples 18 and 19 are much easier to look at, but far from perfect. First of all, you need to be very careful about capitalization. The compiler is case sensitive when formatting date strings. When entering a format string, refer back to Table 9-6 so that you get it right.
Now let’s look at a couple problems with formatting dates. According to the documentation, using a single “h”, “m” or “s” will not put leading zeros in front of the hour, minute or second. This is shown to be true in Example 17. However, you can also see that it does insert a leading space. Even though we specified that all the characters are to be adjacent, it inserts spaces anyway. This is not what we wanted and there is not an easy way to fix it. To get around this you have to concatenate the values together using the Hour(), Minute() and Second() functions. Unfortunately, these functions return two decimal places so you have to work a little harder than expected. The solution is shown in Listing 9-2.
‘Demonstrate displaying a time with no leading spaces
Dim Now As Time
Now = CTime("1:2:3 AM")
Formula = CStr(Hour(Now), 0) & ":" & CStr(Minute(Now), 0) & ":" & _
CStr(Second(Now), "0") & " " & CStr(Now, "tt")
‘Now is formatted as "1:5:4 AM"
