search from 3 combobox

Wilder1626
Wilder1626 used Ask the Experts™
on
Hello all

I'm trying to do a search criteria based on 3 combobox. But the 3rd criteria could be based from 3 different column in my access dbase.

The result i have in my code is a syntax error: execution error '-2147217900 (80040e14)'

Do you know what it could be?

Thanks again for you help


Dim sSQL1 As String

Dim oRST1 As ADODB.Recordset

  Set oRST1 = New ADODB.Recordset

    Dim oConnect1 As ADODB.Connection

  Set oConnect1 = New ADODB.Connection


sSQL1 = "SELECT [ORIGIN],[DESTINATION],[DRY VAN],[HEATER],[REEFER] FROM [CP_RAIL 2011 rates] WHERE " _
            & "ORIGIN ='" & Form33.origin_cp_rail.Text & "' AND " _
            & "DESTINATION ='" & Form33.destination_cp_rail.Text & "' and " _
            & "DRY VAN ='" & Form33.commodity.Text & "' or " _
            & "HEATER ='" & Form33.commodity.Text & "' or " _
            & "REEFER ='" & Form33.commodity.Text & "'"
            
        

            oConnect1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & parameter.cp_rail.Text

 

oRST1.Open sSQL1, oConnect1

 
          Form33.CP_RAIL_rate.DataField = ""

          Set Form33.CP_RAIL_rate.DataSource = oRST1

        Form33.CP_RAIL_rate.DataField = "DRY VAN" Or Form33.CP_RAIL_rate.DataField = "HEATER" Or Form33.CP_RAIL_rate.DataField = "REEFER"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
One of the following should work.  I think your primary problem is your unbracketed DRY VAN column reference.
sSQL1 = "SELECT [ORIGIN],[DESTINATION],[DRY VAN],[HEATER],[REEFER] FROM [CP_RAIL 2011 rates] WHERE " _
            & "ORIGIN ='" & Form33.origin_cp_rail.Text & "' AND " _
            & "DESTINATION ='" & Form33.destination_cp_rail.Text & "' and " _
            & CHR(34) & Form33.commodity.Text & CHR(34) & " IN ([DRY VAN], HEATER, REEFER)"

====
sSQL1 = "SELECT [ORIGIN],[DESTINATION],[DRY VAN],[HEATER],[REEFER] FROM [CP_RAIL 2011 rates] WHERE " _
            & "ORIGIN ='" & Form33.origin_cp_rail.Text & "' AND " _
            & "DESTINATION ='" & Form33.destination_cp_rail.Text & "' and " _
            & "[DRY VAN] ='" & Form33.commodity.Text & "' or " _
            & "HEATER ='" & Form33.commodity.Text & "' or " _
            & "REEFER ='" & Form33.commodity.Text & "'"

Open in new window

Hello

I have tried both and now, i have execution error '-2147217913 (80040e07)'

oRST1.Open sSQL1, oConnect1

Open in new window


Do you know why?
Top Expert 2014

Commented:
any descriptive text accompanying the error message?
Success in ‘20 With a Profitable Pricing Strategy

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!

The error message is:

Type of data incompatible in  expression of criteria.
Top Expert 2014

Commented:
check the data types of your columns
Oh, you are right.

There where columns with money setting. I switch to text and now, that look's good.

But i now have another error at the end of the code.

run time error 13: type incompatible on this part of the code:
 Form33.CP_RAIL_rate.DataField = "DRY VAN" Or Form33.CP_RAIL_rate.DataField = "HEATER" Or Form33.CP_RAIL_rate.DataField = "REEFER"

Open in new window

Top Expert 2014
Commented:
What are you trying to do?

What is the rule for setting the Form33.CP_RAIL_rate.DataField?

=====
If I had to guess, it looks like you are trying to set the datafield to which ever of the fields matched your Form33.commodity.Text value.  Unfortunately, that wouldn't work because each row might match on a different field.
OK.
In my first picture, you will see how my dbase is done.

I have a column for Origin, one for destination and and one for each commodity: DRY VAN, HEATER and REEFER.

In the second picture, you will see that i need to feed the 3 combobox and it would give me the rate for this match.

The challenge is that the commodity could be on 3 different column in my dbase.
DBase.JPG
Rate-result.JPG
I will then put all the rate in the same column and also for the commodity.

That way, it should work like this:

Dim sSQL1 As String

Dim oRST1 As ADODB.Recordset

  Set oRST1 = New ADODB.Recordset

    Dim oConnect1 As ADODB.Connection

  Set oConnect1 = New ADODB.Connection


sSQL1 = "SELECT [ORIGIN],[DESTINATION],[Commodity],[Rate] FROM [CP_RAIL Master] WHERE " _
            & "ORIGIN ='" & Form33.origin_cp_rail.Text & "' AND " _
            & "DESTINATION ='" & Form33.destination_cp_rail.Text & "' and " _
            & "Commodity ='" & Form33.commodity.Text & "'"
            
        

            oConnect1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & parameter.cp_rail.Text

 

oRST1.Open sSQL1, oConnect1

 
          Form33.CP_RAIL_rate.DataField = ""

          Set Form33.CP_RAIL_rate.DataSource = oRST1

        Form33.CP_RAIL_rate.DataField = "Rate"

 Form33.CP_RAIL_rate.Text = Format(Form33.CP_RAIL_rate, "0$")

Open in new window

Top Expert 2014

Commented:
1. Are you comparing some value on a form to these three columns?
2. [Commodity] is not a column in your table (record source)
3. Is the Commodity combobox used solely for the purpose of identifying what column's values you want shown in the Form33.CP_RAIL_rate control?
what i did is to put all 3 commodities in the same column and all the rates in Rate column.
rate.JPG
Top Expert 2014

Commented:
you could do it either way.

from the original data configuration, you would assign the field name = combobox value.
Thanks again for your help.

I will only use 1 column for commodity.

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