VB6, CR 8.5 DateTime Parameter Value Error 20553

[scottke]

I am trying to pass a datetime value from VB to CR but am getting an error. I can pass a string value with no problem but the string "between" parameter value selection sucks. Any suggestions?

Dim strSQL As String
Dim dtStartDate As Date
Dim dtEndDate As Date

Dim strStartDate As Date
Dim strEndDate As Date

Dim dtMonth As Integer
Dim dtYear As Integer

Private Sub Command1_Click()

'clear out old data
Me.CrystalReport1.DiscardSavedData = True
Me.CrystalReport1.Reset

'reset all good params
Me.CrystalReport1.ReportFileName = "N:\Tims\REPORTS\Audit Report - Deleted Entries.rpt"
Me.CrystalReport1.Connect = "uid=tims;pwd=tims;"
Me.CrystalReport1.PrintFileType = crptRecord
Me.CrystalReport1.ReportSource = crptReport
Me.CrystalReport1.ReportTitle = "Audit Report - Deleted Entries"
Me.CrystalReport1.WindowTitle = "Reports: Audit Report - Deleted Entries"

'get the start and stop date for the report
GetDates Trim(str(Me.cboMonth.ListIndex + 1)), Trim(Me.cboYear.Text)

Me.CrystalReport1.ParameterFields(0) = "startdate;" & dtStartDate & ";false"
Me.CrystalReport1.ParameterFields(1) = "enddate;" & dtEndDate & ";false"

'MsgBox Me.CrystalReport1.ParameterFields(0) & " " & Me.CrystalReport1.ParameterFields(1)
Me.CrystalReport1.WindowState = crptMaximized
Me.CrystalReport1.Action = 1

End Sub

Private Sub Form_Load()

Dim strEndDay As String
Dim startYear As Integer
Dim endYear As Integer

Dim strMonth(1 To 12) As String
Dim i As Integer

strMonth(1) = "January"
strMonth(2) = "February"
strMonth(3) = "March"
strMonth(4) = "April"
strMonth(5) = "May"
strMonth(6) = "June"
strMonth(7) = "July"
strMonth(8) = "August"
strMonth(9) = "September"
strMonth(10) = "October"
strMonth(11) = "November"
strMonth(12) = "December"

'fill the combo box with the string array
For i = 1 To 12
Me.cboMonth.AddItem (strMonth(i))
Me.cboMonth.ItemData(Me.cboMonth.NewIndex) = i
Next

'get today's date month and year
dtMonth = DatePart("m", DateTime.Now) - 1
dtYear = DatePart("yyyy", DateTime.Now)

endYear = dtYear
startYear = endYear - 10

'set the display to the current month
Me.cboMonth.ListIndex = dtMonth

'fill the year combo box
For i = startYear To endYear
Me.cboYear.AddItem (str(i))
Me.cboYear.ItemData(Me.cboYear.NewIndex) = i
Next

'set the display to the current year
Me.cboYear.ListIndex = 10

End Sub

Function CheckForLeapYear(intYear As Integer)

'if the current year is completely divisible by 4 with no remainder then
'it is a leap year
If (intYear Mod 4 = 0) Then
CheckForLeapYear = True
Else 'not a leap year
CheckForLeapYear = False
End If

End Function

Sub GetDates(strMonth As String, strYear As String)

Dim isLeapYear As Boolean
Dim strEndDay As String

'get the starting date for the current request
Select Case cboMonth.ListIndex + 1
Case 1, 3, 5, 7, 8, 10, 12
strEndDay = "31"
Case 2
isLeapYear = CheckForLeapYear(dtYear)
If isLeapYear = True Then
strEndDay = "29"
Else
strEndDay = "28"
End If
Case Else
strEndDay = "30"
End Select

'strStartDate = strMonth & "/1/" & strYear & " 00:00:00AM"
'strEndDate = str(strMonth) & "/" + Trim(strEndDay) + strYear + " 23:59:59PM"

strStartDate = strMonth & "/1/" & strYear & " 00:00:00AM"
strEndDate = strMonth & "/" & strEndDay & "/" & strYear & " 23:59:59PM"

dtStartDate = CDate(strStartDate)
dtEndDate = CDate(strEndDate)

'MsgBox strStartDate & Chr(12) & Chr(13) & strEndDate
MsgBox dtStartDate & Chr(12) & Chr(13) & dtEndDate

'MsgBox strStartDate & " " & strEndDate

End Sub

 

[scottke]

finally figured it out...

Dim strSQL As String
Dim dtStartDate As Date
Dim dtEndDate As Date

Dim strStartDate As Date
Dim strEndDate As Date

Dim dtMonth As Integer
Dim dtYear As Integer

Private Sub Command1_Click()

Dim sql As String
Dim yBeginDate As String
Dim yEndDate As String

'clear out old data
Me.CrystalReport1.DiscardSavedData = True
Me.CrystalReport1.Reset

'get the start and stop date for the report
GetDates Trim(str(Me.cboMonth.ListIndex + 1)), Trim(Me.cboYear.Text)

sql = "({AuditTable.AuditDate} >= DateTime("
sql = sql + str(Year(dtStartDate))
sql = sql + "," & Month(dtStartDate)
sql = sql + "," & Day(dtStartDate)
sql = sql + "," & Hour(dtStartDate)
sql = sql + "," & Minute(dtStartDate)
sql = sql + "," & Second(dtStartDate) & "))"
sql = sql + " AND ({AuditTable.AuditDate} <= DateTime("
sql = sql + str(Year(dtEndDate))
sql = sql + "," & Month(dtEndDate)
sql = sql + "," & Day(dtEndDate)
sql = sql + "," & Hour(dtEndDate)
sql = sql + "," & Minute(dtEndDate)
sql = sql + "," & Second(dtEndDate) & "))"

'reset all good params
Me.CrystalReport1.SelectionFormula = sql
Me.CrystalReport1.ReportFileName = "N:\Tims\REPORTS\Audit Report - Deleted Entries.rpt"
Me.CrystalReport1.Connect = "uid=xxxx;pwd=xxxx;"
Me.CrystalReport1.PrintFileType = crptRecord
Me.CrystalReport1.ReportSource = crptReport
Me.CrystalReport1.ReportTitle = "Audit Report - Deleted Entries"
Me.CrystalReport1.WindowTitle = "Reports: Audit Report - Deleted Entries"

yBeginDate = "DateTime(" & Year(dtStartDate) & "," & Month(dtStartDate) & "," & Day(dtStartDate) & "," & Hour(dtStartDate) & "," & Minute(dtStartDate) & "," & Second(dtStartDate) & ")"
yEndDate = "DateTime(" & Year(dtEndDate) & "," & Month(dtEndDate) & "," & Day(dtEndDate) & "," & Hour(dtEndDate) & "," & Minute(dtEndDate) & "," & Second(dtEndDate) & ")"

Me.CrystalReport1.Formulas(0) = "startDate = " & yBeginDate
Me.CrystalReport1.Formulas(1) = "endDate = " & yEndDate

'launch the report
Me.CrystalReport1.WindowState = crptMaximized
Me.CrystalReport1.Action = 1

End Sub

Private Sub Form_Load()

Dim strEndDay As String
Dim startYear As Integer
Dim endYear As Integer

Dim strMonth(1 To 12) As String
Dim i As Integer

strMonth(1) = "January"
strMonth(2) = "February"
strMonth(3) = "March"
strMonth(4) = "April"
strMonth(5) = "May"
strMonth(6) = "June"
strMonth(7) = "July"
strMonth(8) = "August"
strMonth(9) = "September"
strMonth(10) = "October"
strMonth(11) = "November"
strMonth(12) = "December"

'fill the combo box with the string array
For i = 1 To 12
Me.cboMonth.AddItem (strMonth(i))
Me.cboMonth.ItemData(Me.cboMonth.NewIndex) = i
Next

'get today's date month and year
dtMonth = DatePart("m", DateTime.Now) - 1
dtYear = DatePart("yyyy", DateTime.Now)

endYear = dtYear
startYear = endYear - 10

'set the display to the current month
Me.cboMonth.ListIndex = dtMonth

'fill the year combo box
For i = startYear To endYear
Me.cboYear.AddItem (str(i))
Me.cboYear.ItemData(Me.cboYear.NewIndex) = i
Next

'set the display to the current year
Me.cboYear.ListIndex = 10

End Sub

Function CheckForLeapYear(intYear As Integer)

'if the current year is completely divisible by 4 with no remainder then
'it is a leap year
If (intYear Mod 4 = 0) Then
CheckForLeapYear = True
Else 'not a leap year
CheckForLeapYear = False
End If

End Function

Sub GetDates(strMonth As String, strYear As String)

Dim isLeapYear As Boolean
Dim strEndDay As String

'get the starting date for the current request
Select Case cboMonth.ListIndex + 1
Case 1, 3, 5, 7, 8, 10, 12
strEndDay = "31"
Case 2
isLeapYear = CheckForLeapYear(dtYear)
If isLeapYear = True Then
strEndDay = "29"
Else
strEndDay = "28"
End If
Case Else
strEndDay = "30"
End Select

dtStartDate = strMonth & "/1/" & strYear & " 00:00:01AM"
dtEndDate = strMonth & "/" & strEndDay & "/" & strYear & " 23:59:59PM"

'MsgBox dtStartDate & Chr(12) & Chr(13) & dtEndDate

End Sub