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!
search from 3 combobox
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
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"
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 & "'"
Hello
I have tried both and now, i have execution error '-2147217913 (80040e07)'
Do you know why?
I have tried both and now, i have execution error '-2147217913 (80040e07)'
oRST1.Open sSQL1, oConnect1
Do you know why?
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:
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"
What are you trying to do?
What is the rule for setting the Form33.CP_RAIL_rate.DataFi
=====
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.
What is the rule for setting the Form33.CP_RAIL_rate.DataFi
=====
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
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:
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$")
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?
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
rate.JPG
Premium Content
You need an Expert Office subscription to comment.Start Free Trial