ADOQuery filter

ADOQuery filter

Post by Alan » Thu, 19 Apr 2007 13:24:02


How do I make the filter something like 'LIKE' in SQL?
eg.
Name = 'John*'
so returns 'Johnny', 'Johnson', 'johnny', 'johnson'
 
 
 

ADOQuery filter

Post by Paul Scot » Thu, 19 Apr 2007 21:08:33

On Wed, 18 Apr 2007 05:24:02 +0100, Alan T < XXXX@XXXXX.COM >



In SQL(server) (and Access-via-ADO) the wild card is a percent "%" not an
asterisk

name like 'John%'

--
Paul Scott
Information Management Systems
Macclesfield, UK.

 
 
 

ADOQuery filter

Post by Alan » Fri, 20 Apr 2007 09:15:37

Thanks.

Can I make it case insensitive?



On Wed, 18 Apr 2007 05:24:02 +0100, Alan T < XXXX@XXXXX.COM >



In SQL(server) (and Access-via-ADO) the wild card is a percent "%" not an
asterisk

name like 'John%'

--
Paul Scott
Information Management Systems
Macclesfield, UK.
 
 
 

ADOQuery filter

Post by Asim » Fri, 20 Apr 2007 12:47:05

>

Hi Alan,

It is case insensitive only by default, but in case you want it to be
case sensitive use COLLATE

Name COLLATE SQL_Latin1_General_CP1_CS_AS like 'John%'

with results like 'Johnny', 'Johnson'
excluding 'johnny', 'johnson'

Regards,

Asim Khan

--- posted by geoForum on http://www.yqcomputer.com/
 
 
 

ADOQuery filter

Post by Alan » Sat, 21 Apr 2007 13:21:13

Sorry, what I want to do is not using SQL but set the Filter property of the
ADOQuery component.
 
 
 

ADOQuery filter

Post by Asim » Sat, 21 Apr 2007 14:48:35

Hi Alan,

Set Filtered := True;

and EITHER implement the following method, something like

procedure TForm.ADOQueryFilterRecord(DataSet: TDataSet;
var Accept: Boolean);
begin
Accept:=(DataSet.FieldByName('State').Value='CA') or
(DataSet.FieldByName('State').Value=Null)
end;

OR

Filter := 'State = ' + QuotedStr('CA') + ' OR ' +
'State = NULL';


Regards,

Asim Khan

--- posted by geoForum on http://www.yqcomputer.com/
 
 
 

ADOQuery filter

Post by Steve Zimm » Thu, 26 Apr 2007 12:55:53

This works with MS SQL Server and the SQLOLEDB provider.

AdoQuery1.Filter = 'Name like ''John%'' ';
AdoQuery1.Filtered := True ;

-Steve-
 
 
 

ADOQuery filter

Post by Alan » Tue, 15 May 2007 11:54:32

Thanks.
It works.