HELP!! - Passing a date range parameter to a report

[SmileyFace]

I am having problems passing a date range to a report. Please help. I have to pass a shipnumber called custshp to the report along with the report name in this case ship5.rpt and a date range. When passing it I am getting the error message: Error Reading Records: A date-time is required here.

Now my report has a datefield and I need to pass a range. I have 2 questions:

1) In SQL server my dates are stored as mm/dd/yyyy and the report pulls out the date from a table in this database. Howver if I do 'Browse field data' on the field in the report it shows me the date like this mm/dd/yyyy 12:00:00AM. It has this same time attached to every date. Why is this happenning? Is this why I get that error?

2) Here is the code. Can someone tell me if I am passing the parameters the right way?

dim Report
Report = trim(lcase(Request.QueryString("ReportName&quot)) & ".rpt"

' CREATE THE APPLICATION OBJECT
On error resume next
If Not IsObject(session("oApp&quot) or session("oApp&quot is nothing Then
Set session("oApp&quot = Server.CreateObject("CrystalRuntime.Application&quot
End If ' CREATE THE REPORT OBJECT
'
'The Report object is created by calling the Application object's OpenReport method.

Path = Request.ServerVariables("PATH_TRANSLATED&quot
While (Right(Path, 1) <> &quot;\&quot; And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend

if session(&quot;debug&quot = &quot;Y&quot; then
Response.Write &quot;exec:Al Path=&quot; & path & &quot;<br>&quot; & vbcrlf
end if

'OPEN THE REPORT (but destroy any previous one first)

If IsObject(session(&quot;oRpt&quot) then
Set session(&quot;oRpt&quot = nothing
End if

On error resume next
if session(&quot;debug&quot = &quot;Y&quot; then
Response.Write &quot;Report=&quot; & path & report & &quot;<br>&quot; & vbcrlf
end if
Set session(&quot;oRpt&quot = session(&quot;oApp&quot.OpenReport(path & report, 1)
'This line uses the &quot;PATH&quot; and &quot;reportname&quot; variables to reference the Crystal
'Report file, and open it up for processing.

If Err.Number <> 0 Then
Response.Write &quot;Error Occurred creating Report Object: &quot; & err.number & Err.Description
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Response.write &quot;<script language=JavaScript>&quot;
Response.write &quot;setTimeout(&quot;&quot;window.close()&quot;&quot;, 1500)&quot;
Response.Write &quot;</script> &quot;
'Session.Abandon
Response.End
End If

session(&quot;oRpt&quot.MorePrintEngineErrorMessages = False
session(&quot;oRpt&quot.EnableParameterPrompting = False
session(&quot;oRpt&quot.DiscardSavedData

ShipNum = Request.QueryString(&quot;custshp&quot
Datadateback = Date() - 5
DatadateCurr = Date()

if report = &quot;ship5.rpt&quot; then
SelectionFormula = &quot;{t_ship5hdr.custshp}= '&quot; & ShipNum & &quot;'&quot;
SelectionFormula = SelectionFormula & &quot; AND {t_ship5dtl.shipent} >= '&quot; & Datadateback & &quot;'&quot;
SelectionFormula = SelectionFormula & &quot; AND {t_ship5dtl.shipent} <= '&quot; & Datadatecurr & &quot;'&quot;
session(&quot;oRpt&quot.RecordSelectionFormula = cstr(SelectionFormula)
end if

On Error Resume Next

session(&quot;oRpt&quot.ReadRecords
if session(&quot;debug&quot=&quot;Y&quot; then
Response.Write &quot;exec: MoreRequiredsteps.asp&quot;
end if
If Err.Number <> 0 Then
Response.Write &quot;Error Occurred Reading Records: &quot; & Err.number & Err.Description
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Response.write &quot;<script language=JavaScript>&quot;
Response.write &quot;setTimeout(&quot;&quot;window.close()&quot;&quot;, 1500)&quot;
Response.Write &quot;</script> &quot;
'Session.Abandon
Response.End
Else
If IsObject(session(&quot;oPageEngine&quot) Then
set session(&quot;oPageEngine&quot = nothing
End If
set session(&quot;oPageEngine&quot = session(&quot;oRpt&quot.PageEngine
End If

Thanks in advance!

 

[Turkbear]

Hi,
Dates are stored ( in SqlServer, I believe and Oracle ( for sure) ) with a time component , so it is actually a datetime field, with the time set to Midnight if not specified when loaded.

What format are your Datadateback and
Datadatecurr variables?

Be sure to pass a Crystal-Recognized Date format to the report..

 

[SmileyFace]

Well, the dates inside the SQL server table in this case are without the time attached. Crystal seems to be converting it when pulling it in. How can I handle this?

The DataDateBack and DataDateCurr are both of the format mm/dd/yyyy. I just added a trim on it like below but it still gives me the error: A date-time is required here! Please help.

Datadateback = trim(lcase(Date() - 5))
DatadateCurr = trim(lcase(Date()))

 

[Turkbear]

Hi, Have you tried to revise the code so that it passes the dates in Crystal date input format? - like
Date(Datadateback)


Just a thought..

 

[SmileyFace]

How would I do this? Could you elaborate on the code? THANKS for helping!

 

[Turkbear]

Hi,
Try concatenating the Function to the variable:

DtBack = "Date('" & Datadateback & "')"
DtCur = "Date('" & Datadatecurr & "')"

Use those vars instead of your originals in the SF construction..

With some tweeking you should be able to get it to work ( the Quotes, etc may need some revision)

 

[SmileyFace]

Hey! Instead of playing around with the code I created a stored procesure which selected the records from my table for the range needed. It was a good option since the date range was between currentdate-5 and currentdate and not a user-selected date. I don't have to pass date parameters then. So I redid my report instead to read the records from the stored procedure and it works smoothly now! Thanks for your suggestions anyways.

 

[Turkbear]

Hi,
No problem..One of my maxims is 'let the database do as much of the work as it can'