[RESOLVED] Pass Date Parameters from VB6 to CR XI

I've spent the last couple hours searching for information on how to do this but haven't found an answer I can understand. Some of the better replies have been "look in the help file" - there is nothing about this in the help file ... or "Don't use parameters". Sheesh. Or links to the old Business Objects website that now go to a generic SAP page. I'm frustrated with that approach so I'm going to ask here.

Anyway, I designed the report in CRXI's main report designer program and then added it to my VB6 project as a dsr and form - it's the developer edition of CRXI. On the report I created two parameter fields, "Start Date" and "End Date". They are the dates between which I want the records to be shown. This works just fine (the parameter input screen is shown when starting the report) except I do not want to have the user enter the dates because a form already asks for that information. Instead I just want to pass the dates selected from dtPickers to the parameters in the report. There has to be a pretty simple way to do this. Maybe I'm just dense, but I can't understand half of the replies I've read talking about dlls, and so many of them just stop with no resolution.

The code I have isn't going to be of much use ..

Code:

---

CRViewer1.ReportSource = Report

Report.DiscardSavedData

CRViewer1.ViewReport

---

Is there someone out there who can tell me the command and syntax to use while I still have at least a little hair left?
TIA
Ken

Moved to the Reporting forum.

It's been ages since I worked with Crystal Reports and VB6 but I think you do something like this:

Code:

---

CRViewer1.ParameterFields(0) = "@ParamName;Value"

---

Maybe you don't need to pass parameters to the report. The report has an property called Database that has a table collection. To each table you can to assign a recordset. If the recordset is open with parameters, the data in the recordset is already filtered.
This must be do like this:
Dim RsFiltered as New ADODB.Recordset
RsFiltered.Open "SELECT * FROM MyTable WHERE Codigo=" & Cstr(xValue), Conexion, adOpenStatic, adLockReadOnly
Report.Database.Tables(1).SetDataSource RsFiltered

Thanks Joacim. Trying all possible syntaxes for that consistently gives me "Object doesn't support this property or method."

Cristian. I may have to just pass my sql statement to the report as it seems passing parameters is just too difficult. My sql statement is somewhat more involved that your example. Unfortunately when I try it with your suggestion I also get the "Object doesn't support this property or method" complaint or I get "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

Code:

---

SQL=SELECT Transactions.DateOfPayment, Transactions.AmountOfPayment, Reservations.Charges, Reservations.MaidRateR, Reservations.Tax FROM Reservations INNER JOIN Transactions ON Reservations.ReservationID = Transactions.ReservationID WHERE DATEOFPAYMENT >=#1/1/1980# AND DATEOFPAYMENT <=#4/11/2013#

---

Perhaps it simply isn't possible and I'll have to make the user put in the dates a second time.

Quote:

---

Perhaps it simply isn't possible and I'll have to make the user put in the dates a second time.

---

That's why it's better not to use parameters in rpts that are called by an application
We ask for the dates in our own vb6 forms and just construct the RecordSelectionFormula and pass to the rpt before we call it

Anyway, I forgot to show how to pass parameters to rpt from vb6 app
The rpt read data from a sql server (it doesn't matter) but could be from any db

References:
Crystal Reports Viewer Control
Crystal Reports ActiveX Designer Run Time Library

Code:

---

' Typical Variable Declarations
dim crApp as New CRAXDRT.Application
dim crRept as New CRAXDRT.Report
dim crParamDefs as CRAXDRT.ParameterFieldDefinitions
dim crParamDef as CRAXDRT.ParameterFieldDefiniton
dim crDBTab as CRAXDRT.DatabaseTable

' Open Report File
set crRept = crApp.OpenReport("WHATEVER.RPT")

' Logon to SQL server
crRept.Database.LogonServer "p2ssql.dll", "server name", "database name", "userid", "userpassword"

' Set table locations (because my reports run against multiple servers)
foreach crDBTab in crRep.Database.Tables
  crDBTab.SetLogonInfo "server name", "database name", "userid", "userpassword"
next

' Disable Parameter Prompting for the end user
crRep.EnableParameterPrompting = FALSE

' Gather the list of available parameters from the report
set crParamDefs = crRep.ParameterFields

' Loop through all parameters in the report by name, filling in the appropriate parameter with the right value
foreach crParamDef in crParamDefs
  select case crParamDef.ParameterFieldName
    case "SubTitle"
      crParamDef.SetCurrentValue "My Report Subtitle Goes Here"
    case "@BeginDate"
      crParamDef.SetCurrentValue datevalue(txtBeginDate)
    case "@EndDate"
      crParamDef.SetCurrentValue datevalue(txtEndDate)
    case "@IntegerParam"
      crParamDef.SetCurrentValue val(int(txtIntegerParam))
  end select
next
crViewer1.ReportSource = crRept
crViewer1.viewReport

---

You can pass any value you want to CR, whether the user typed it or your program generated it in this way.
(Parameters that begin with @ are for use with stored procedures)

Thanks for the reply JG. I found something of an easier workaround though. I removed the two parameters and added this:

Code:

---

Report.RecordSelectionFormula = "{Transactions.DateOfPayment} >= " & "Date (" & Format(frmRevenue.dtStart, "YYYY,mm,dd") & ")" & " AND {Transactions.DateOfPayment} <= " & "Date (" & Format(frmRevenue.dtEnd, "YYYY,mm,dd") & ")"

---

It took a few times through to get the syntax right, but this works as I intended.

Outside of some bug testing, this completes the project .. although I have three more in the wings.

Thanks again. I will keep your code on hand for the next time I might need it. I don't find CR very friendly as far as the VB interface is concerned ... it would be nice if there were a help file specifically for this.

Ken