***** Null Values (Include or skip?) *****

[Trancedified]

I'm having trouble working with a SQL Server 2000 field that has a space called:

Support date

Another problem is with a table full of <Nulls>. In my RecordSelectionFormula: I have #'s and CDate's because the field's datatype is nvarchar, not DateTime.

Two questions:

1) How can I include this field in my RecordSelectionFormula:

Support date

Use square brackets? [ ]

2) Regarding the <Nulls> in the database, I know of the IsNull function but how would I create an "If....Then....Else" statement that will either a) skip over the <nulls> or b) include the <nulls>?

Here is my code so far, there are two DateTimePickers so that I will be able to filter through specific dates.

Private Sub cmdSupportDate_Company_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSupportDate_Company.Click

Dim strStartDate As String
Dim strEndDate As String
'Starting Date
strStartDate = DateTimePickerSupportDate_CompanyStart.Value
strEndDate = DateTimePickerSupportDate_CompanyEnd.Value
'Ending Date

Dim objSupportDate_Company As CrystalDecisions.CrystalReports.Engine.ReportDocument

objSupportDate_Company = New crSupportDate_TicketNumber()

objSupportDate_Company.DataDefinition.RecordSelectionFormula = "CDate({[TD3.Support date]}) >=#" & strStartDate & "# and CDate({[TD3.Support date]}) <=#" & strEndDate & "#"

ReportViewerSupportDate_Company.ReportSource = objSupportDate_Company

ReportViewerSupportDate_Company.RefreshReport() 
'Refresh report based on new date range criteria

End Sub

Thanks in advance,

Chris

[coolbiz]

1. Square backets but just for the fieldname (TD3.[Support date]).

2. For DB field, you might wanna use IsDBNull() function instead. Lets say you have 2 tables, INCIDENTREC and INCIDENTHIST. After you select a record in INCIDENTREC, lets say you want to find all the incidents on the date specified by the DateLogged field of the record that you've just selected. You'd construct something like:

Dim searchDate As DateTime = IIf(IsDBNull(incRec.DateLogged), DateTime.Now, incRec.DateLogged)

' use the search date in your search criteria
...

You can also construct a regular IF ... THEN statement:

If (Not IsDBNull(incRec.DateLogged)) Then
  searchDate = incRec.DateLogged
Else
  MessageBox.Show("DATELOGGED is NULL! Cannot search!")

  ' maybe exit?
  ...
End If

-Cool Bizs

[Trancedified]

coolbiz,

I tried putting the square brackets around the field name, but when I ran the date filter, a message box tells me "This field name is not known"

        objSupportDate_Company.DataDefinition.RecordSelectionFormula = "CDate({TD3.[Support date]}) >=#" & strStartDate & "# and CDate({TD3.[Support date]}) <=#" & strEndDate & "#"

If I haven't mentioned it, the CDate function has to be there because the Datatype for the field is nvarchar (SQL Server 2000), not DateTime.

Start & EndTime is determined by a DateTimePicker calendar control.

I removed the time out of the values of the DateTimePicker so it will just be a date in this format:

12/31/2004



Any other solutions?

Thanks!

Chris

[coolbiz]

You might wanna check w/ crystal report documentation regarding specifying field name w/ spaces in them. For SQL statement, that should have been good.

-Cool Bizs