Big Query Assistance Required

Hi !
One of possible solutions :

with ADOQuery1 do
   begin
   Close;
   SQL.Clear;
   SQL.Add('Select * from motors where ');
   if copy(AnsiUpperCase(ComboBox1.text),1,3) <> 'ANY' then
      begin
      SQL.Add('Make = :p1');
      Parameters.Parambyname('p1').value := ComboBox1.text;
   end;
   if copy(AnsiUpperCase(ComboBox2.text),1,3) <> 'ANY' then
      begin
      if pos('where', SQL.Text) = 0 then SQL.Add(' where Model like :p2')
      else                               SQL.Add('   and Model like :p2');
      Parameters.Parambyname('p2').value := ComboBox2.text + '%';
   end;
   if copy(AnsiUpperCase(ComboBox3.text),1,3) <> 'ANY' then  //supposing price is string !!
      begin
      if pos('where', SQL.Text) = 0 then SQL.Add(' where Price like :p3')
      else                               SQL.Add('   and Price like :p3');
      Parameters.Parambyname('p3').value := ComboBox3.text + '%';
   end;
   if copy(AnsiUpperCase(ComboBox4.text),1,3) <> 'ANY' then
      begin
      if pos('where', SQL.Text) = 0 then SQL.Add(' where left(postcode,3) = :p4')
      else                               SQL.Add('   and left(postcode,3) = :p4');
      Parameters.Parambyname('p4').value := copy(ComboBox4.text,1,3);
   end;
   Open;
end;

Select allOpen in new window

oops, small mistake : line 5-10 corrected
 
   SQL.Add('Select * from motors');
   if copy(AnsiUpperCase(ComboBox1.text),1,3) <> 'ANY' then
      begin
      SQL.Add(' where Make = :p1');
      Parameters.Parambyname('p1').value := ComboBox1.text;
   end;

--sorry--

MerijnB:

Your code compiles but I get the error below when I run the query

Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', or 'UPDATE'

Any ideas???

what is the final result (query) you get?

It doesn't go ahead and execute the query.

Messagebox:

Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', or 'UPDATE'

Comes out and doesn't continue.

Coould it be that after your code...I have this:

if not sCheckBox1.Checked then
   begin
  ADOQuery1.SQL.Add('UNION  ALL SELECT TOP 200 * from motors where Make <> "' + (ComboBox1.Text) + '"');
   end;

  ADOQuery1.Active := true;
   ADOQuery1.Open;

   if ADOQuery1.EOF  then
  begin
    sMessageDlg('Sorry No Matches Found! Please try another search!', mtInformation, [mbOK], 0);

  Exit;
end;  

I mean the query (string) you sent to the DB

Pehaps it's worth to try my solution also.

I just run the code you posted :o/

Query := 'select * from mytable' + WhereClause;

I'm I being a n00b again? :o(

nope, but I can't see what's in the different comboboxes.

so what is in Query after you did:

Query := 'select * from mytable' + WhereClause;

Actually the query was:

Query := 'select * from motors' + WhereClause;

post the contents of Query

I think I don't understand what I needd to do here...

Could you post a dummy Query so I can see please?

Something like this???

 ADOQuery1.SQL.Add('select * from motors where (Make) = "' + (sco.Text) + '"');

Sorry....I should have posted this here:

procedure TForm1.Button12Click(Sender: TObject);
 var WhereClause: string;
    WhereClauseParts: TStringList;
    i: integer;
    Query: string;
begin
   
 
  ADOQuery1.active := false;
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
 
  /////////////////////////////////////////////////////////////
 
 
begin
 WhereClauseParts := TStringList.Create();
 
 // check make combobox
 if sco.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Make = %s)', [QuotedStr(sco.Text)]));
 
 // check model combobox
 if sModels.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Model like "%%%s%%")', [sModels.Text]));
 
 // check price combobox
 if sPrice.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Price <= %s)', [sPrice.Text]));
 
 // check postcode combobox
 if sPostCode.Text <> 'ANY' then
  WhereClauseParts.Add(Format('(Postcode like "%s%%")', [Copy(sPostCode.Text, 1, 3)]));
 
 // now combine all where clauses
 if WhereClauseParts.Count > 0 then
 begin
  WhereClause := ' where';
  for i := 0 to WhereClauseParts.Count - 1 do
  begin
   WhereClause := WhereClause + ' ' + WhereClauseParts[i];
 
   if i < WhereClauseParts.Count - 1 then
    WhereClause := WhereClause + ' AND';
  end;
 end;
 
 WhereClauseParts.Free();
 
 
 
 // now we have the where clause, make the rest of the query
 Query:=('select * from motors' + WhereClause);
end;
 
 
 
 
 
 ////////////////////////////////////////////////////////////////
 
   if not sCheckBox1.Checked then
   begin
  ADOQuery1.SQL.Add('UNION  ALL SELECT TOP 200 * from motors where Make <> "' + (sco.Text) + '"');
   end;
 
//  ADOQuery1.Active := true;
 //  ADOQuery1.Open;
 
   if ADOQuery1.EOF  then
  begin
    sMessageDlg('Sorry No Matches Found! Please try another search!', mtInformation, [mbOK], 0);
 
  Exit;
end;
 
 
 
 
end;

I don't see you use the contents of the variable Query anywhere.

Query:=('select * from motors' + WhereClause);

Also Tried:

 Query:=('select * from motors' + WhereClause + '(Make) = "' + (sco.Text) + '"');

But got no results back....Hmmm

the variable Query is only a string, it doesn't do anything by itself.

You'll need to execute the string on the database to get anything back from it

probably something like:
ADOQuery1.SQL.Add(Query);

Right!!!!

Got it now!!! :o)   Sorry for being sooo Damn!

The only thing that I cannot get to work is the Price.

I get an error when it's not ANY

Error is: Data Type mismatch in ceteria expression...It is to do with the fact that the Price is actually a String?

please post the contents of the field query (the sql query you actually sent to the DB) and the type definition of the field price in the database

It's a CSV file it's reading the data from.

That's the main problem with Price :o/

can you post some records from the CSV file?

Sorted!  I managed to change the data type to Integer!  
Thanks a million for your help!!!!
Works perfect!!!