Simple Data Types

Basic syntax supports the standard simple data types that we expect in a language: Boolean, Number, Currency, String, DateTime, Date, and Time.

Notice that rather than have a large number of numeric data types such as integer, double, etc., there is simply a single data type called Number. There is no need to worry about whether the number will use a decimal point or what its largest value is.

The Currency data type is treated the same as a Number data type with a few exceptions. They are listed below:

Currency can only have two decimal places. If assigned a number with more than two decimal places, it will round up to the nearest penny.

Currency automatically gets formatted as a monetary value. This eliminates the overhead of you always having to format the variable whenever it gets printed.

Since Currency is a different data type, it must be converted to a number to be used in mathematical assignments using non-currency variables. See the section “Converting Data Types” for more information.

Strings use the double quote, “, to specify a string literal. A character is represented by a string of length one. Referencing a position within a string is Base 1. Thus, if you want to refer to the first character in a string, you would use an index of 1.The maximum length of a string constant is 65,534 characters. Information on using the Basic syntax built-in string functions is in the next chapter.

‘Demonstrate assigning a string constant to a variable
Dim Var As String
Var = "This is a string"

Dates are a little unusual in that there are three different data types available. The Date type can only store a date and the Time type can only store a time. It’s preferable to use these data types if you don’t need both values stored in a variable. If you do need both types in the same variable, use the DateTime type. Designate a DateTime constant by surrounding it with the # sign.

Dim MyBirthday As DateTime
MyBirthday = #5/23/1968#

Null Values

The Null value isn’t a data type, but a type of data. It is a non-value used to mark when a field doesn’t have a specific value stored in it. For example, if you are filling out a data entry form, there might be certain personal questions that you skip because you aren’t comfortable answering them. When the program saves your answers to the database, it might put a null value in the questions you skipped to mark that you didn’t answer them. Unfortunately, databases are meant to have data in them and using the null value can wreak havoc on your reports and formulas. If there is a chance that you are reporting from a database that has null values in it, you need to account for that so that your report runs smoothly.

In most cases you want to convert nulls to their default values. The default value is determined by the data type of the field being used. These values are shown in Table 8-1. There are two settings that control this and they are both found by selecting the menu options Crystal Reports > Report > Report Options.The first option, Convert Database NULL Values to Default, converts nulls when reading data from the database. The second option,Convert Other NULL Values to Default, applies to non-database null values (such as formulas). Check both of these options to keep null values from impacting your report.

There are times when you don’t want to convert nulls to their default values because you want to know whether the data field is empty or if no value was saved at all. In this circumstance you have to be careful when using conditional statements that involve fields with null values. Crystal Reports will stop the evaluation and return no results. You need to test for this using theIsNull() function and handle the results correctly.

The IsNull() function returns true when the field is null and returns false for any other value. The following example tests whether the ID field is null or not. If so, it returns the dummy value of 9999.

If IsNull({Sales.PersonID}) Then
9999
Else
{Sales.PersonID};

If you want to use the IsNull() function in conjunction with another test, you have to perform the IsNull() test first. Otherwise, the report could error out. The next example tests whether a valid ID was entered.

If IsNull({Sales.PersonID}) OR {Sales.PersonID}=0 Then
"This ID is not valid"
Else
"The ID is valid";

In this example, if a field has a null value, the IsNull() function returns true and the test for zero doesn’t need to be evaluated. But if the test for zero was performed first, the test would fail immediately when the null value was encountered.

Many times, a report will want to look ahead one record and see what a field’s upcoming value is. Again, you need to test for null values. This is done with theNextIsNull() function.

If NextIsNull({SalesOrders.ShipDate} Then
"This is the last order shipped.";

ThePreviousIsNull() function performs the same test as the NextIsNull() function. But it looks at the previous record.

Question: I am trying to show a text field that says, “NONE”, if no records are selected.  Has anyone done this before?

Answer: To determine if a report doesn’t have any records, use the IsNull() function to test if the total number of records is null. Right-click the text field that has “NONE” in it, and select Format Text Object. Add this conditional formula to the Suppress property:

formula = Not(IsNull(Count({Employee.Last Name})))