Filtering more than one record in database table throws up syntax error/access violation

Question

Using Delphi XE2.

I have a database software package which displays records from a table onto a cxgrid. I have implemented a filter button with at a click of it allows users to search for results using specific records. At the moment it only works when 1 of the records is selected, it doesn't like it when more than one of the filter records is selected, it displays the following error....'syntax error or access violation: near 'and' in...[and]'. The following code is what I'm doing at point of clicking the filter button.

Any help would be much appreciated.

begin
  with dmData.aQry do
begin
  Close;
  SQL.Clear;
  SQL.Text:= ('select * from DBA.RECORDS');
  if dbluCaseCategory.Text <> '' then SQL.Add('where category_type like :category_type');
  if dbluSubCategory.Text <> '' then SQL.Add('and sub_cat_type like :sub_cat_type');
  if dbluCustomer.Text <> '' then SQL.Add('and customer_name like :customer_name');
  if dbluUsername.Text <> '' then SQL.Add('and created_by_user like :created_by_user');
  if cxStartDateEdit.Text <> '' then SQL.Add('and logged_dt like :logged_dt');

  if dbluCaseCategory.Text <> '' then ParamByName('category_type').Value := dbluCaseCategory.Text +'%';
  if dbluSubCategory.Text <> '' then ParamByName('sub_cat_type').Value := dbluSubCategory.Text +'%';
  if dbluCustomer.Text <> '' then ParamByName('customer_name').Value := dbluCustomer.Text +'%';
  if dbluUsername.Text <> '' then ParamByName('created_by_user').Value := dbluUsername.Text +'%';
  if cxStartDateEdit.Text <> '' then ParamByName('logged_dt').Value := cxStartDateEdit.Text +'%';
  Open;
end;
  Close;
end;

Answer 1

Your code will only work if you include the first filter (dbluCaseCategory.Text) because you add the where sentence only on that part. So if you don't pass any value to the dbluCaseCategory the final SQL sentence will not be valid. In order to fix that just add a Where 1=1 in the first sentence. like so.

  with dmData.aQry do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from DBA.RECORDS Where 1=1');
    if dbluCaseCategory.Text <> '' then SQL.Add('and category_type like :category_type');
    if dbluSubCategory.Text <> '' then SQL.Add('and sub_cat_type like :sub_cat_type');
    if dbluCustomer.Text <> '' then SQL.Add('and customer_name like :customer_name');
    if dbluUsername.Text <> '' then SQL.Add('and created_by_user like :created_by_user');
    if cxStartDateEdit.Text <> '' then SQL.Add('and logged_dt like :logged_dt');

    if dbluCaseCategory.Text <> '' then ParamByName('category_type').Value := dbluCaseCategory.Text +'%';
    if dbluSubCategory.Text <> '' then ParamByName('sub_cat_type').Value := dbluSubCategory.Text +'%';
    if dbluCustomer.Text <> '' then ParamByName('customer_name').Value := dbluCustomer.Text +'%';
    if dbluUsername.Text <> '' then ParamByName('created_by_user').Value := dbluUsername.Text +'%';
    if cxStartDateEdit.Text <> '' then ParamByName('logged_dt').Value := cxStartDateEdit.Text +'%';
    Open;