When I try to use a parameter with a LIKE statement, I get an error saying that the parameter doesn't exist.

Example code:

 qryTest.SQL.Add('SELECT *');
 qryTest.SQL.Add('  FROM Names');
 qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE ''%:PFirstname%''');
 qryTest.ParamByName('PFirstname').AsString := LowerCase('Jan');

Anyone else have this problem?

Thanks,

Steve

Steve


Replace

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE ''%:PFirstname%''');
qryTest.ParamByName('PFirstname').AsString := LowerCase('Jan');


with

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE :PFirstname);
qryTest.ParamByName('PFirstname').AsString := LowerCase('%Jan%');

or

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE ''%''+ :PFirstname+ ''%'''); sorry if the quotes are wrong
qryTest.ParamByName('PFirstname').AsString := LowerCase('Jan');

The way you have it written you're simply giving a string %:PFirstName% as the item to be tested

Roy Lambert [Team Elevate]

<< Replace

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE ''%:PFirstname%''');
qryTest.ParamByName('PFirstname').AsString := LowerCase('Jan');


with

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE :PFirstname);
qryTest.ParamByName('PFirstname').AsString := LowerCase('%Jan%');

or

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE ''%''+ :PFirstname+ ''%'''); sorry if the quotes are wrong
qryTest.ParamByName('PFirstname').AsString := LowerCase('Jan');

The way you have it written you're simply giving a string %:PFirstName% as the item to be tested >>

Damn it, I don't know why I didn't see that.  Thanks Roy.  It was one of those times where I kept looking at it and couldn't see what was wrong.  The syntax I ended up using was (using single quotes):

qryTest.SQL.Add(' WHERE LOWER(Firstname) LIKE ''%'' + :PFirstname + ''%''');
qryTest.ParamByName('PFirstname').AsString := LowerCase('Jan');

Thanks again.

Steve

Steve


Took me a while to spot it. Putting my glasses on the right way round helped

Roy Lambert