VB:Crystal Reports Record Selection Formulas

How do you set the record selection formula at run time for a crystal report designer(Crystal Reports 7) used within visual basic (VB6) while using variables in the formula?  I'm trying to filter out records under a certain dollar amount and/or quantity based on user input at run time.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Annette RussellDBACommented:
Try this if your report was created using ADO, OLEDB, RDO.
1.  Create a sql statement filtering out the records with the dollar amount and/or quantity from the variable. (ie "SELECT * FROM TABLE where Amount = " & sDollar = sSQL

2.  With the crystalobject set the  following properties:

frmForm.CrystalReport1.SQLQuery = sSQL
frmForm.CrystalReport1.connect = "dsn="& databasename.

The connect property may be different based on the type of database you are using.  Hope this helps .. Good Luck.

If not using ADO, OLE DB, or RDO, try using the SelectionFormula property.
CrazyFoxAuthor Commented:
I'm using ADO in an n-tier environment. But the way the server program is set up restricts what type of queries I can write.  I need to change or set the CrystalReport1.RecordSelectionFormula property.

Are you hitting an Access database or SQL Server. If you are hitting a SQL Server database, consider creating a Parameter Stored Procedure based on the filtered record. Then in Crystal Reports, create a parameter based on the field you want to filter, (Dollar Amount).

Assuming you are using the Crystal ActiveX Control, place some code like below. The parameters in SQL Server are the txtBeginningDate and txtEndingDate.

There are a few parameters in the Crystal Report that are not included in the SQL Server, such as txtHighlight.


Sub CrystalReportConnect(mCrystalFormName As Form)

Dim SelectionFormula As String
Dim strSubHeading As String
Dim mres As String

    If Not IsDate(txtFromDate) Then
        MsgBox "Invalid Date. Please input a valid date.", vbOKOnly + vbCritical, "Print Report"
        Exit Sub
    End If

    If Not IsDate(txtToDate) Then
        MsgBox "Invalid Date. Please input a valid date.", vbOKOnly + vbCritical, "Print Report"
        Exit Sub
    End If

    If CDate(txtFromDate) > CDate(txtToDate) Then
        MsgBox "The Start Date must be on or before the End Date.", vbOKOnly + vbCritical, "Print Report"
        Exit Sub
    End If
    CrystalReport1.Reset 'Resets the report data

    If txtHighlight = "" Then
    '    CrystalReport1.ParameterFields(0) = "Highlight;0;TRUE"
    '    CrystalReport1.ParameterFields(0) = "Highlight;" & txtHighlight & ";TRUE"
    End If  'txtHighlight = ""

    If DrillDown Then
        CrystalReport1.WindowShowGroupTree = True
        CrystalReport1.WindowTitle = "Check Out Those Sales Man!!!"
        CrystalReport1.WindowShowGroupTree = False
        CrystalReport1.WindowTitle = "Check Out Those Sales Man!!!"
    End If

        strSubHeading = "'" & txtFromDate & " through " & txtToDate
        strSubHeading = strSubHeading & ", Sorted By " & cboGrouping
    If txtHighlight = "" Then
    '    strSubHeading = strSubHeading & ", No Highlight'"
        strSubHeading = strSubHeading & ", The Highlight Value Is $" & txtHighlight & ".00 and Greater.'"
    End If
        CrystalReport1.Formulas(1) = "Sub Heading=" & strSubHeading
    Select Case cboGrouping
        Case "City"
            CrystalReport1.GroupCondition(0) = "GROUP1;{SalesVolume.City};"
        Case "Store"
            CrystalReport1.GroupCondition(0) = "GROUP1;{SalesVolume.Store};ANYCHANGE;A"
        Case "Publisher"
            CrystalReport1.GroupCondition(0) = "GROUP1;{SalesVolume.Publisher};ANYCHANGE;A"
    End Select
    With CrystalReport1

    With CrystalReport1
    .Connect = "driver={SQLServer};server=ServerName;uid=sa;pwd=;database=databasename"

    .ReportFileName = "C:\My Documents\VBTraining\TestParameters.rpt"
    .ReplaceSelectionFormula "{SalesVolume.OrdDate} in Date(" & _
     Format(txtFromDate, "yyyy,m,d") & ") to Date(" & _
     Format(txtToDate, "yyyy,m,d") & ")"
    .Destination = crptToWindow
    mres = .PrintReport
        If mres <> 0 Then
              MsgBox "printer error on Crystal report :" & .LastErrorString
        End If
    End With
End Sub
Private Sub cmdPrint_Click()

   CrystalReportConnect frmSalesVolume

End Sub

Private Sub Form_Load()
Dim Today As Date
Today = Format(Now, "mm/dd/yyyy")
txtToDate = Today
cboReport.Text = "Please Select A Report"
cboGrouping.Text = "Please Select A Group"

End Sub

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

CrazyFoxAuthor Commented:
It's an Access database.  
You can still use parameter "queries" (instead of Parameter Stored-Procedures). On the query builder, right click and click on the Parameters option, set your parameters based on your filtering criteria. Note, you still have to go through the Date formatting as Crystal Reports has its own date format, therefore you need to supply the data to Crystal reports as you normally would with SQL Server.

Hi, I'm using VB5 and CR4.6 and Access 97, I think there no much different:
    cryReport.ReportFileName = "C:\mob\Reports\branchdaily.rpt"
    cryReport.SelectionFormula = "{BranchDailyTotal.BranchCode} like '" & frmCriteria1.cboBranchCode & "' and {BranchDailyTotal.Date} = Date(" & Mid(frmCriteria1.mskDate, 7, 4) & ", " & Mid(frmCriteria1.mskDate, 4, 2) & ", " & Mid(frmCriteria1.mskDate, 1, 2) & ")"
    cryReport.UserName = gsUserName
    cryReport.Password = gsPassword
    cryReport.SortFields(0) = "+{BranchDailyTotal.BranchCode}"
    cryReport.SortFields(1) = "+{BranchDailyTotal.TransactionType}"
    cryReport.SortFields(2) = ""

be careful when you build up your sql for selectionformula. add ' for string in front and behind.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CrazyFoxAuthor Commented:
Thanks!  That works with only a minor change. It's cryReport.RecordSelectionFormula in Crystal Reports 7 instead of cryReport.SelectionFormula.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.