<nulls> & Crystal Reports

[Trancedified]

Hello,

I am relatively new to Crystal Reports w/ .NET. I have a database table in SQL Server 2000 that has a few Nulls in the "Date" field. Is there a way to skip over the <nulls> when I'm running a count? For now the report doesn't even load in the CrystalReportViewer because of these nulls. Here is my code so far:

Dim objDate_AccountRep As CrystalDecisions.CrystalReports.Engine.ReportDocument
        objDate_AccountRep = New crDate_AccountRep()
        objDate_AccountRep.DataDefinition.RecordSelectionFormula = "CDate({TD2.Date}) >=#" & strStartDate & "# and CDate({TD2.Date}) <=#" & strEndDate & "#"
        ReportViewerDate_AccountRep.ReportSource = objDate_AccountRep
        ReportViewerDate_AccountRep.RefreshReport()

Is the below code the correct way to exclude a null in my report?

If (isNull({TD2.Date})) Then
        {TD2.Date} & " "
        End if

Thanks!

Chris

[brucevde]

If TD2.Date is a true date field why are you converting it with CDate.

If your database is SQL Server why are surrounding dates with a # (or is this a .NET thing).

What does strStartDate contain - make sure it is in mm/dd/yyyy format.

Try this record selection formula instead (if it is a true date field).

"{TD2.Date} Between '" & strStartDate & "' And '" & strEndDate & "'"

[Trancedified]

The reason for the #'s is because the datatype in SQL Server 2000 is NOT datetime, it's a nvarchar. I wish it was a datetime but that's just how it's setup. So that's why I had CDate.


strStartDate and strEndDate are both string variables that are set equal to a DateTimePicker.


Hope that helps with my explanation to my problem?



Chris

[brucevde]

Null values should not affect the record selection formula that you have posted (they will not be included on the report).

However, there is a report option that may affect the formula. In Crystal 8.5 (sorry I don't have .NET) its on the Report Options dialog box (File menu->Report Options) and is called "Covert Null Field Value to Default" - make sure that item is not checked.

[Trancedified]

brucevde,

Thanks for replying so quickly!

I think my problem may not be with the Null values: it could be with the names of my Table names.

All my other reports work with no-spaced field names like:


FieldOne


However, I have a field that says:

Support date (space in between)


Is there a way in Crystal to allow me to have that space?


Thanks!

[brucevde]

I think it depends on what method you use to connect to the database.

For instance if you are using oledb
A table with a space and Crystal will change the space to an underscore.
A field with a space and Crystal does nothing.

If you are using the native sql server driver
Crystal surrounds both table and field names with square brackets.

The fun part is that all this depends on the "Use Default Alias" option. And to add more fun, you better test each possible scenario because I may have got the above reversed.