TDAMemDataTable filters (Delphi)

To create a filter using the Filter property, set the value of the property to a string that contains the filter's test condition. For example, the following statement creates a filter that tests a dataset's State field to see if it contains a value for the state of California:

table.Filter := 'State = ' + QuotedStr('CA');

You can also supply a value for Filter based on text supplied by the user. For example, the following statement assigns the text from edit box to Filter:

table.Filter := Edit1.Text;

You can, of course, create a string based on both hard-coded text and user-supplied data:

table.Filter := 'State = ' + QuotedStr(Edit1.Text);

Filters can compare field values to literals and to constants using the following comparison and logical operators:

Boolean operators

Operator	Meaning	Usage
AND	Tests two statements that both have to be True	(fld1>10) AND (fld1<30)
NOT	Tests one statement that has to be not True	NOT (fld1=10)
OR	Tests that at least one of two statements is True	(fld1=11) OR (fld1=5)
XOR	Tests that at least one of two statements is not equal to the other one	(fld1=11) XOR (fld2=5)

Comparing operations

Operator	Meaning	Usage
<	Less than	fld1 < 10
>	Greater than	fld1 > 10
>=	Greater than or equal to	fld1 >= 10
<=	Less than or equal to	fld1 <= 10
=	Equal to	fld1 = 10
<>	Not equal to	fld1 <> 10
IS NULL	Test values for NULL	fld1 IS NULL
IS NOT NULL	Test values for NOT NULL	fld1 IS NOT NULL
IN	Tests values from specified list	fld1 IN (10,20)
LIKE	Finds a string fitting a certain description	fld1 LIKE 'a*' fld1 LIKE '%a'

Wildcards for partial comparisons for LIKE operator

Operator	Usage
*	fld1 LIKE 'abc*abc'
%	fld1 LIKE '%abc%'

Logical operators and math symbols

Operator	Meaning	Usage
AND	bitwise and	(fld1 AND 10)=10
NOT	bitwise negation	(NOT fld1)=31
OR	bitwise or	(fld1 OR 10)=40
XOR	bitwise xor	(fld1 XOR 10)=0
+	addition	(fld1 + 10)=30
-	subtraction	(fld1 - 10)=10
*	multiplication	(fld1 * 10)<30
/	real division	(fld1 / 10)>10
%	modulus	(fld1 % 2)=1
$	specifies that value in hex format	(fld1 + $10)<100

Methods (since .1611)

Method	Meaning	Usage
Convert(value,data_type)	Converts an expression of one data type to another. see list below	Convert(fld1,'Int32')=10
DatePart(value,part)	Returns an integer representing the specified datepart (sec, min, hour, day, week, month, year) of the specified date	DatePart(fld1,'year')=2025
Iif(condition,value1,value2)	Uses short-circuit evaluation to conditionally return one of two values	Iif(fld1>20,fld2,fld1)=10
IsNull(expression,value)	Returns the specified value IF the expression is NULL, otherwise return the expression	IsNull(fld1,fld2)='a'
Len(value) Length(value)	Returns the number of characters in a string	Len(fld1)>5 Length(fld1)>5
LowerCase(value)	Converts an string to lowercase	LowerCase(fld1)='a'
Now()	Returns the current date and time (accurate only to the nearest second)	fld1<Now()
Today()	Returns the current date	fld1=Today()
Trim(value)	Trims leading and trailing spaces and control characters from a string	Trim(fld1)='a'
TrimLeft(value)	Trims leading spaces and control characters from a string	TrimLeft(fld1)='a'
TrimRight(value)	Trims trailing spaces and control characters from a string	TrimRight(fld1)='a'
SubString(value,index,count)	Returns a substring of a one-based string	SubString(fld1,1,2)='ab'
UpperCase(value)	Converts an string to uppercase	UpperCase(fld1)='A'

Supported types for Convert method

System.Char	Char	System.String	String
System.Byte	Byte	System.SByte	SByte
System.Int16	Int16	System.UInt16	UInt16
System.Int32	Int32	System.UInt32	UInt32
System.Int64	Int64	System.UInt64	UInt64
System.Double	Double	System.Single	Single
System.DateTime	DateTime

By using combinations of these operators, you can create fairly sophisticated filters. For example, the following statement checks to make sure that two test conditions are met before accepting a record for display:

(fld1 > 1400) AND (fld1 < 1500)