Filtering Crystal Report in VB using combobox and user input (not Crystal Report parameters)

[SHERWO]

New at programming and Crystal Reports.

I have created a report where on the VB form there are 3 dropdowns, 3 textboxes and a search button which allows the user to then specify search parameters in textboxes below each of the dropdown boxes. I've managed to get the first 2 working (using the textboxes to match user input for first and last name) but am struggling with the third, which has "Greater than", "Less than" and "Equals" as the dropdown options. The user then inputs a number into the text box and searches the db for values that match. I've been working on this for hours and no matter what I try the search either doesn't come up with anything or I get a conversion error. See code below. Any help is greatly appreciated.

        Dim firstname As String
        Dim lastname As String
        Dim searchFirst As String
        Dim searchLast As String
        Dim lastyearsales As Double
        Dim searchSales As Double


        firstname = txtFirstName.Text
        lastname = txtLastName.Text
        lastyearsales = CDbl(txtLastYearSales.Text)

        'User inputs filters for first name
        If cboFirstName.SelectedIndex = 0 Then
            searchFirst = firstname & "*"
        ElseIf cboFirstName.SelectedIndex = 1 Then
            searchFirst = "*" & firstname
        ElseIf cboFirstName.SelectedIndex = 2 Then
            searchFirst = "*" & firstname & "*"
        ElseIf cboFirstName.SelectedIndex = 3 Then
            searchFirst = firstname
        End If

        'User inputs filters for last name
        If cboLastName.SelectedIndex = 0 Then
            searchLast = lastname & "*"
        ElseIf cboLastName.SelectedIndex = 1 Then
            searchLast = "*" & lastname
        ElseIf cboLastName.SelectedIndex = 2 Then
            searchLast = "*" & lastname & "*"
        ElseIf cboLastName.SelectedIndex = 3 Then
            searchLast = lastname
        End If


        'User inputs filters for sales data (****THIS IS WHERE THE TROUBLE OCCURS****)

        'If "Greater than" chosen
        If cboLastYearSales.SelectedIndex = 0 Then
            searchSales = "{Sales_SalesPerson.SalesLastYear}" > lastyearsales

            'If "Less than" chosen
        ElseIf cboLastYearSales.SelectedIndex = 1 Then
            searchSales = "{Sales_SalesPerson.SalesLastYear}" < lastyearsales

            'If "Equals" chosen
        ElseIf cboLastYearSales.SelectedIndex = 2 Then
            searchSales = "{Sales_SalesPerson.SalesLastYear" = lastyearsales
        End If

        Dim firstnamesearch As String
        Dim lastnamesearch As String
        Dim lastyearsalesearch As Double

        firstnamesearch = "{Person_Contact.FirstName} like """ & searchFirst & """"
        lastnamesearch = "{Person_Contact.LastName} like """ & searchLast & """"
        lastyearsalesearch = "{Sales_SalesPerson.SalesLastYear}" = searchSales

        Dim reportToView As New CrystalReport1

        reportToView.DataDefinition.RecordSelectionFormula = firstnamesearch
        reportToView.DataDefinition.RecordSelectionFormula = lastnamesearch


        Me.CrystalReportViewer1.ReportSource = reportToView

 

[Auguy]

Just my two cents. Maybe it would be easier to select the records into a dataset in your program and then pass the dataset to Crystal. This is the method I use for all of my Crystal reports. Plus I think it's easier to debug because you probably already have some stored or common procedures to select the data for other areas of your program.

myReport.SetDataSource(dsRpt1)

Auguy
Sylvania/Toledo Ohio