×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VB6, CR 8.5 DateTime Parameter Value Error 20553

VB6, CR 8.5 DateTime Parameter Value Error 20553

VB6, CR 8.5 DateTime Parameter Value Error 20553

(OP)
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
 

RE: VB6, CR 8.5 DateTime Parameter Value Error 20553

(OP)
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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close