Crystal Reports - Passing Date Range

vbjohn
vbjohn used Ask the Experts™
on
Does anyone know how to pass a Date Range Parameter in VB 6.0 to Crystal Reports?

this is what I have for a single date seletion:
crptYTDAP.ParameterFields(0) = "DateSelection;" & txtSDate & ";FALSE"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Add a second parameter and pass in the second date.  In the report filter the data using the dates.

How are calling the report?  Through the ActiveX control or the RDC?
What other code do you have?

I usually do this through the SelectionFormula property of the report as the where clause.  If you desire this informatiion I'll try to find the code in the morning.

mlmcc
Crystal 6.0 syntax for date is Date(yyyy,mm,dd), so change your code to...

crptYTDAP.ParameterFields(0) = "DateSelection;Date(" & format(txtSDate.text,"yyyy,mm,dd") & ");TRUE"

Hope this helps...

Author

Commented:
I am calling it through ActiveX.


    crptYTDAP.ParameterFields(0) = "DateSelection;" & txtSDate & ";FALSE"
    crptYTDAP.Action = 1
    crptYTDAP.WindowShowGroupTree = True



jamesckay:  I need it for a date range not just a single date.  could you show me a date range, if not then thanks anyways.


John-
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Here is how I pass 2 date parameters to the report

    cr_Generic_Report.ParameterFields(0) = txt_First_Param_Name & ";Date(" & _
                            Mid(txt_First_Date, 7, 4) & "," & _
                            Mid(txt_First_Date, 4, 2) & "," & _
                            Mid(txt_First_Date, 1, 2) & _
                            ")Time(00,00,00,000);True"
    cr_Generic_Report.ParameterFields(1) = txt_Second_Param_Name & ";Date(" & _
                            Mid(txt_Second_Date, 7, 4) & "," & _
                            Mid(txt_Second_Date, 4, 2) & "," & _
                            Mid(txt_Second_Date, 1, 2) & _
                            ")Time(00,00,00,000);True"

The report is based on a stored procedure that uses the dates in a range.

Another method is to use the REPLACESELECTIONFORMULA method of the OCX control.

Something like this

dim strSQL as string

strSQL = "{CRViewName.FieldName} in 2002/02/01 to 2002/02/28"

CRrpt.REPLACESELECTIONFORMULA strSQL

good luck
mlmcc

Author

Commented:
Since I do not use Stored Procedures, I tried the other one and it seems like I am unable to pass a date range into 1 parameter.  Is there any good references out there that I can look up?


John-
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
With the second method why do you still need a parmeter?
You should be able to do it like this

strSQL = "{CRViewName.FieldName} in  " & cstr(txtSDate) & " to " & cstr(txtEDate )

mlmcc
For date range then you should have 2 parameters instead of one... say DateFrom and DateTo.. Then pass in :-

crptYTDAP.ParameterFields(0) = "DateFrom;Date(" & format(txtFromDate.text,"yyyy,mm,dd") & ");TRUE"
crptYTDAP.ParameterFields(1) = "DateTo;Date(" & format(txtToDate.text,"yyyy,mm,dd") & ");TRUE"

crptYTDAP.selectionformula = "{yourtable.yourdatefield}
IN {?DateFrom} TO {?DateTo}"

Author

Commented:
mlmcc,

Yours is working.  I tried it and it worked out fine.  But then instead of hardcoding in the dates I put this in:

SelectionFormula = "{GL20000.TRXDATE} In Date(" & _
         Format(txtSDate, "yyyy,m,d") & ") To Date(" & _
         Format(txtEDate, "yyyy,m,d") & ")"

and now I get:

Run-Time error '424': Object Required

What does this mean?

John-

Author

Commented:
Thanks for all your help!  I had a major brain fart...

It works now.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad to help.

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial