SQL Select with VB controls parameteres

[ringobingo]

Hi,
Kind of new at this so here goes, i have these list box's filled with only distinct values from colums in an access database. These Distinct values are from a simple Select Distinct qry. I have then created a
Select qry like below so user can pull what the want from a VB form

sExecutestring = "Select * From Carsdb"
"Where FirstColumn = '"ListBox.List(Listbox.ListIndex) & "'" "And SecondColumn = '"ListBox1.List(Listbox1.ListIndex) = SecondColumn & "'" etc

I have a few questions that i would be greatful for their answers, - Can i use these two select queries in this way?
and what is the correct syntax for the Where section.

As it stands i have this query set, that looks cool in all the text and material gathered but i still get this Error

Run Time Error --2147217900
Syntax Error in From Clause

Cheers

Andy

 

[FLUZZI]

Hi there RingoBingo!

I Don't if I'm wrong but I think is something is missing in your SQL.

1st line:
sExecutestring = "Select * From Carsdb[put a space here]"

second line:
"Where FirstColumn = '"ListBox.List(Listbox.ListIndex)
third line:
& "'" "And SecondColumn = '"ListBox1.List
forth line:
& "'" "[put 'and' here](Listbox1.ListIndex) = SecondColumn & "'" etc

I not a user of VB(so I can make a mistake) but in Delphi it's like this:

sExecutesString:= 'select * from Cardb ' +
'Where FirstColumn='+ IntoToStr(ListBox.ItemIndex) ' +
'And SecondColumn='+ IntoToStr(ListBox1.ItemIndex)'...etc


I Try

 

[Ruairi]

fluzzi is right, your syntax is a little off. Here is how you need to format it. You were missing some spaces and &'s.

sExecutestring = "Select * From Carsdb Where FirstColumn = '" & ListBox.List(Listbox.ListIndex) & "'" & " And SecondColumn = '" & ListBox1.List(Listbox1.ListIndex)& "'"

 

[crystalized]

You might also want to try a stored procedure with the input parameters that you need. Although how you work with stored procedures in VB, I don't know (it may be more trouble than it is worth).