How to pass VB parameter to SQLQuery in Crystal Report.

See these:
http://support.crystaldecisions.net/library/kbase/articles/c2004910.asp
http://support.crystaldecisions.net/library/kbase/articles/c2002292.asp

Emoreau,
  I unable to access those page, any problem with the address?

I think you could do this by setting the recordselectionformula from VB but I do it by declariung a formula field in the report to hold the value - setting that in VB and utilising it in the report.

I just accessed them without problems! Here is a paste of both.

Knowledge Base
 Article ID:
c2004910

Created:
1999/11/02

Reviewed :
2000/06/16
 

 
 
 
 
 
Using parameters to change the logic of the record selection criteria

The information in the article refers to:
Seagate Crystal Reports 7
 
 
Applies to:
 
Reported version and lower
Version 5, 6 and 7
Seagate Info Report Designer
Using parameters to create dynamic selection formulas
 

Synopsis


A report needs the ability to change the logic of its record selection formula based on a user-specified parameter value. For example, you can choose whether the record selection criteria is for a database field to be greater than a value, or for it be less than a value, depending on what you enter for the parameter:

  Select {field} > Value or
  Select {field} < Value

Solution

You can do this by creating a parameter field and then creating an 'if then else' statement in the record selection formula.

NOTE:=======

Using a 'If then Else' formula for your record selection prevents the record selection formula from being passed on to the SQL statement's WHERE clause. This means the record selection process will occur in SCR, instead of in the database. This could significantly increase the time it takes your report to process.

=============

1. Create a string parameter {?condition}, setting the default values to:

<
>
<=
>=
<>

2. Go to Report | Edit Selection Formula, and select Record. Create the following formula:

Note: this example uses the value 10,000 for the record comparisons, although this value could also be parameterized as well, for greater flexibility.

If {?condition} = "<" then
         {database field} < 10000 else
If {?condition} = ">" then
         {database field} > 10000 else
If {?condition} = "<=" then
          {database field} <= 10000 else
If {?condition} = ">=" then
          {database field} >= 10000 else
If {?condition} = "<>" then
{database field} <> 10000;

This selects records using a criteria based on the value entered for the parameter.



--------------------------------------------------------------------------------

Category:
 Report Designer , Report Designer , Designers , Designers
 
Subject:
 Filtering Data , Parameter Fields , Info Report Designer , Info Report Designer
 
Topic:
 Record Selection , Crystal Parameter , Filtering Data , Parameter Fields
 
Keywords:
 RECORD SELECTION PARAMETER CHANGE LOGIC SELECTION CRITERIA
 
 
 


--------------------------------------------------------------------------------

Status:
 Verified
 
Product:
 Crystal Reports Professional
 
Reported Version:
 7.0.1.192 Crystal Reports Professional
 
Applies to:
 Reported version and lower
 
Bit Version:
 16 & 32 Bit
 
Applies To (Database):
 All
 
 
========================================================

Knowledge Base
 Article ID:
c2002292

Created:
1998/12/04

Reviewed :
2001/02/13
 

 
 
 
 
 
Passing Selection Formulas & Formulas at Runtime using Automation Servers

The information in the article refers to:
Report Designer Component 7
 
 
Applies to:
 
Reported version and higher
Formula Fields
Selection Formula

 

Synopsis

Passing Selection Formulas and Formulas at Runtime using the Crystal Report Engine Automation Server (Cpeaut32.dll) and the Crystal Report Designer Component Automation Server (Craxdrt.dll).

Solution

NOTE: Formulas must conform to Crystal Reports' formula syntax.(i.e. strings embedded in single quotes while numbers not, etc). For Crystal Reports to parse a formula it receives from the application (Visual Basic in this case), the formula must be identical to a formula that you would enter directly into the report designer.

  Use the MsgBox function in Visual Basic to verify that the formula string being sent from VB is in a format that can be used by Crystal Reports.

The remainder of this document shows some sample code, demonstrating the use of the SelectionFormula and Formulas() Properties of the CPEAUT32.DLL and CRAXDRT.DLL.

Passing Selection Formula to the Crystal Report using CPEAUT32.DLL
================================================

Selection Formula
----------------------------
Essentially when passing the selection formula to Crystal Reports using the CPEAUT32.DLL, the .RecordSelectionFormula property of the Report object is utilized.

When hardcoding the selection formula, the code can be directly copied from the Crystal Report and pasted into the code as follows:

Sub Command1_Click()
    CrpReport.RecordSelectionFormula = "{Customer.Customer ID} = 1"
    CrpReport.Preview
End Sub

If, however, you would like to replace the value with a variable, the code would look like the following:

Sub Command1_Click()
    'x can be a textbox being populated with an integer
    Dim x as integer
    CrpReport.RecordSelectionFormula = "{Customer.Customer ID}=" & x
    CrpReport.Preview
End Sub

This particular example demonstrates passing a integer variable to Crystal Reports, if however, the variable is type string, it is slightly different:

Sub Command1_Click()
    'x can be a textbox being populated with an integer
    Dim x as string
    CrpReport.RecordSelectionFormula = "{Customer.Customer ID}=" & chr(34) & x & chr(34)
    CrpReport.Preview
End Sub

Any dates that are being passed to Crystal Reports must be in Crystal Reports Date format, Date(yyyy,mm,dd).

FormulaFields
----------------------
When passing a value to Crystal Reports, the .Text property of the FormulaFieldDefinition object is used.

First, in Crystal Reports, create your formula. If you are giving it a string value, you must place a string value in it. This will force Crystal to treat the formula as a string. A single space inside double quotes is a good idea, as this will not print if left unchanged--yet it holds the string type for when you change the actual value later from VB. Place the new formula on your report where you would like the passed value displayed.

If you will be sending a numeric value or date, again place an appropriate value in the formula in order to establish the data type of the formula. To pass a numeric value to a formula, place 0 in the formula when you create the formula in Crystal Reports. To pass a date value to a formula, place the Today function in the formula.


Consider that you would like to send a title to your report from your VB application. The following is the suggested syntax to change the title at runtime. As instructed above, a formula is inserted at the top of the report, and is named Title. It presently contains a space inside quotes, as in " ".

Sub Command1_Click ()
    'if we hard-code the title
    CrReport.FormulaFields.Item(1).text = " 'This is a Title' "
    'If we use the value of the Textbox
    Text1.Text = "This is a Title"
    CrReport.FormulaFields.Item(1).text = chr(34) & text1.text & chr(34)
    CrReport.Preview
End Sub

Now you would like to send a numeric value to your report. The following code changes the value of a numeric formula at runtime.

Sub Command1_Click ()
    'if we hard-code the numeric value
     CrReport.FormulaFields.Item(1).text = "9"
    'If we use the value of a variable
     x = 65
     CrReport.FormulaFields.Item(1).text = x
     CrReport.Preview
End Sub

Passing Selection Formula and Formulas using CRAXDRT.DLL
===========================================

Passing the selection formula to the Crystal Report using the CRAXDRT.DLL is almost the same. Except for fully qualifying the report objects differently, the syntax is essentially the same. If for example, you are passing the selection formula from the form_load event, the code would look like the following:

'General Declarations
Dim crxReport as New CrystalReport1

Private Sub Form_Load()
CRViewer1.ReportSource = report
report.RecordSelectionFormula = "{Customer.Customer ID} = 1"
CRViewer1.ViewReport
End Sub

The same rules apply to the CRAXDRT.DLL in regards to passing strings, numbers and dates (see above.)

Like the RecordSelectionFormula, passing the values to formula fields are basically the same as the CPEAUT32.DLL except for fully qualifying the variables differently. See below:

'General Declarations
Dim crxReportas New CrystalReport1
Dim CrFormulaFields as CRAXDRT.FormulaFields
Dim CrFormulaField as CRAXDRT.FormulaField

Private Sub Form_Load()
CRViewer1.ReportSource = report
Set CrFormulaFields = report.FormulaFields
Set CrFormulaField = CrFormulaFields.Item(1)
CrFormulaField.Text = " 'This is a String' "
CRViewer1.ViewReport
End Sub

Again, the same rules in regards to datatypes apply for passing values to formula fields as it does for CPEAUT32.DLL.



--------------------------------------------------------------------------------

Category:
 Development , Development
 
Subject:
 Report Designer Component (RDC) , Automation
 
Topic:
 Formulas , Formulas
 
Keywords:
 SCRCOM, RECORDSELECTIONFORMULA, FORMULA, AUTOMATION SERVER, RDC
 
 
 


--------------------------------------------------------------------------------

Status:
 Reported Issue
 
Product:
 Crystal Reports Professional
 
Reported Version:
 7.0.1.192 Crystal Reports Professional
 
Applies to:
 Reported version and higher
 
Bit Version:
 32 Bit
 
Reported OS and Version:
 Windows 95
 
Dev Tool:
 Visual Basic
 
 

The easiest way would be to change the SelectionFormula property of the Report control before you preview the Report through VB.  There are many other ways.  One way would be to base your report on a Stored procedure, and then set the StoredProcParams before you preview the report (this is my favorite way to do it).  Or, you can create a couple of Formula fields in your report, and in the report you build a selection formula that selects records in the range of date 1 to date 2.  This is a little more work.

Back to the easy way.  Here is an example of exactly what you asked for:

Assume that you have a text box where they enter the beginning date of the date range (From Date) and another for the ending date of the date range (To Date) and let's say that your table name was called Orders and your date field is called OrderDate.  Then, your SelectionFormula would look like this.

Dim vFromDate as variant
Dim vToDate as variant

vFromDate = txtFromDate.Text
vToDate = txtToDate.Text

Report1.SelectionFormula = "{Orders.OrderDate} in " & _
"Date(" & Year(vFromDate) & ", " & Month(vFromDate) & ", " & _
Day(vFromDate) & ") to " & _
"Date(" & Year(vToDate) & ", " & Month(vToDate) & ", " & _
Day(vToDate) & ")"

Report1.Action = 1

If you put a breakpoint on that line, then when you display the contents of Report1.SelectionFormula (after executing that statement) the contents should look like:

{Orders.OrderDate} in Date(2001,7,4) to Date(2001,7,11)

Is belim still around?

Good question.  By the way, Belim, don't try manipulating the SQLQuery, it never seems to work.  Stick to the SelectionFormula, or to StoredProcParams....

Any news now?

Questions Asked 6
Last 10 Grades Given A B B  
Question Grading Record 3 Answers Graded / 3 Answers Received

It's time to clean up this topic area and that means taking care of this question. Your options at this point are:
 
1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.
 
2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
 
3.  Ask Community Support to help split points between participating experts.  Just comment here with details.
 
4.  Delete the question because it is of no value to you or to anyone else.  To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
 
If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there.  We also request that you review any other open questions you might have and update/close them.  Display all your question history from your Member Profile to view details.
 
PLEASE DO NOT AWARD THE POINTS TO ME.
 
____________________________________________
 
 
 
Hi Experts:
 
In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question.  Likewise, you can also suggest that I PAQ or delete the question.
 
Experts, please do not add further "answer" information to this question.  I will be back in about one week to finalize this question.
 
Thank you everyone.
 
Moondancer :)
Community Support Moderator @ Experts Exchange
 
P.S.  Engineering has been advised about the error in the comment date/time sort order.

I deserve the points

Force accepted by moderator.  In the event Asker returns with additional needs related to this question, please respond and continue the collaboration process.
Thank you,
Moondancer
Community Support Moderator @ Experts Exchange