We help IT Professionals succeed at work.

Using Filter option in TADOQuery

mudi_ji
mudi_ji asked
on
i m facing a problem using filter option of TAdoQuery object. String in filters work fine but when i try to use filter on dates, it throws an exception.
i m trying to fetch records from database using TADOQuery object and then applying filters at runtime. Problem occurs when i apply filter on date column. i have a table named orders, and there is a column orderDate. Now when i need to apply filter to get orders between two given dates, i do following steps;

qryOrders.close;
qryOrders.filter := 'OrderDate between ''' + DateToStr(dtPkr1.Date) + ''' and ''' + DateToStr(dtPkr2.Date) + '''';
qryOrders.filtered := true;
qryOrders.Open;

when code executes, following EOleException is thrown:
"Arguments are of wrong type, are out of acceptable range or are in conflict with one another"

now thats right as i m converting date to string in filter and actual column is of datatype datetime, but i need to do so as filter accepts only strings. i also tried using # character instead of using quotes to enclose date in, (as in case of crystal reports) but it again gives same error.
can anyone help me plz ?
Comment
Watch Question

Sounds like the date is not in the correct format to your database.

Try playing around with formatting

'OrderDate between ' + QuotedStr( FormatDateTime('dd/mm/yyyy', dtPkr1.Date ) +
' and ' + QuotedStr( FormatDateTime('dd/mm/yyyy', dtPkr2.Date )'

Check your database settings for the correct date format.
Might be 'mm/dd/yyyy' or something similar
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
  Hi!

Check your OrderDate field datatype.
If your OrderDate field is a Date field or DateTime then you should use
filter like this

qryOrders.filter := 'OrderDate between ' + dtPkr1.Date + ' and ' + dtPkr2.Date;

Most likely you have an Date or DateTime datatype in your OrderDate field and that is
causing this error.

Regards,
  Tomas Helgi
You cant use the date field directly in the filter property as it is a string field and not a date field.
This is why he needs to use the FormatDateTime, and to correctly match it to his database date format.
Hi,

I wouldn't use filter with TADOQuery but will create an appropriate SELECT statement in run-time with WHERE clause (MSSQL syntax):

with ADOQuery1 do begin
  Close;
  SQL.Clear;
  SQL.Add('SELECT * FROM MyTable WHERE');
  SQL.Add('OrderDate BETWEEN');
  SQL.Add('CONVERT(DATETIME, '+ QuotedStr(FormatDateTime('dd.mm.yyyy',dtPkr1.Date)) + ', 104)');
  SQL.Add('AND CONVERT(DATETIME, '+ QuotedStr(FormatDateTime('dd.mm.yyyy',dtPkr2.Date)) + ', 104)');
  Open;
end;

Regards, Geo

Author

Commented:
@mikelittlewood;
i've tried doing it the way u suggested but it still throws same exception. date format is correct in both places as it is working fine when i pass date as argument to any query. e.g. if i use a where clause in sql statement and then pass argument value at runtime, then it will work fine. like i've done this at another place where i used query : 'SELECT * from orders where orderDate between :dt1 and :dt2'
and i pass values of 'dt1' and 'dt2' as:

qryOrders.parameters.paramByName('dt1').value := dtpkr1.date;
qryOrders.parameters.paramByName('dt2').value := dtpkr2.date;
qryOrders.Open;

this works fine. but when i try the above method of filters, it gives problem. now in this second method, while passing argument, same datatype is passed as that of column datatype i.e. date, so everything goes fine. so problem lies when we pass date as string. so i tried the method suggested by geobul, partialy. i.e., i changed the filter string to:
qryOrders.filter := 'orderDate between CONVERT(DATETIME, '+ QuotedStr(FormatDateTime('mm/dd/yyyy',dtFrom.Date)) + ', 104) and CONVERT(DATETIME,' + QuotedStr(FormatDateTime('mm/dd/yyyy',dtTo.Date)) + ', 104)'

now it should be converting the string to date at runtime, but this results in same exception again !!!
as the date format by default is mm/dd/yyyy and i haven't changed it so i m using the same format. i've tried the other format of dd/mm/yyyy too but result is same.
so what else can be done ?

@Tomas, filter property is of type String and we can't concatenate date to a string without converting it to string.

@geobul, i can't do so as i m having large queries at certain places. i mentioned just an example here. and also its not only the date filter, filter selection is open to user at run time so he can add as many as he wants. its more feasable to use filter property instead of re-writing SQL statement
Format mm/dd/yyyy is 101 not 104. But you can't use SQL functions in the filter because the filtering process works at local (app level) not at SQL server level.
BTW have you tried:

qryOrders.filter := 'OrderDate >= ' + FloatToStr(dtPkr1.Date) + ' and OrderDate <= ' + FloatToStr(dtPkr2.Date);

Regards, Geo

Author

Commented:
that worked but with DateToStr instead of FloatToStr.
although the problem is solved but what is problem with 'between' function ?
Not supported in filtering. Between is a SQL structure.