Similar search in integer field by filter property

Question

I have a ADODataSet where the "Filtered" property is set to True (Filtered:= True;)

When I apply the filter:

[No] like '2%'

an error "Filter can't be opened" pops up. [No] is a field in the ADODataSet of integer type. When I apply a similiar filter to string columns it works fine.

e.g:

[LastName] like 'Jo%'. 

Any idea ?

Thanks.

Answer 1

I agree with Ken, if you're looking for numeric values 20 through 29 or 200 through 299, then search based on the values. If you still want to do as you ask, consider that the Filter property of a tAdoDataSet is not identical to adding a "where" clause to your query. A where-clause would be dealt with on the server side, using the server's syntax. The Filter property, on the other hand, is parsed within your software and has its own syntax rules.

Option one is to use an actual where-clause. In my test I'm using MS SQL Server. I changed the SQL text to:

select [LASTNAME], [NO] from PEOPLE
where [NO] like '2%'

In this case, the syntax rules for MSSQL will cast the numeric value to a character string before applying the filter.

Option two is to alter the query to return a string.

select [LASTNAME], [NO],
cast ( [NO] as varchar(20) ) as [NO_AS_CHAR] 
from PEOPLE

And then change the filter to

[NO_AS_CHAR] like '2%'