search from 3 combobox

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 & "'"

Select allOpen in new window

Hello

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

oRST1.Open sSQL1, oConnect1

Select allOpen in new window

Do you know why?

any descriptive text accompanying the error message?

The error message is:

Type of data incompatible in  expression of criteria.

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"

Select allOpen in new window

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$")

Select allOpen in new window

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

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.