I have a Query

say

SELECT Client, Company, TheDate from MyTable   --> Works fine

then if I

SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND TheDate<= :toDate

MyQuery.ParamByName('fromDate').AsDate := DateTimePicker1.Date;  
MyQuery.ParamByName('toDate').AsDate := DateTimePicker2.Date;
MyQuery.Open;

Works fine BUT when I am using a Parameter Date search like this, if one of the records
has a NULL value
the Query hangs.

I have tried SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND
TheDate<=:toDate AND TheDate IS NOT NULL

But it still happens. If I set the date range to records where I know that TheDate is not
NULL it works fine but as soon as TheDate has a NULL value
it hangs.

I need some records to have this NULL value and even when I include "TheDate IS NOT NULL"
it still hangs.

Am I doing something wrong? Thanks

Dave <david@itfx.com.au> wrote:

I have a Query

say

SELECT Client, Company, TheDate from MyTable   --> Works fine

then if I

SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND TheDate<= :toDate

MyQuery.ParamByName('fromDate').AsDate := DateTimePicker1.Date;  
MyQuery.ParamByName('toDate').AsDate := DateTimePicker2.Date;
MyQuery.Open;

Works fine BUT when I am using a Parameter Date search like this, if one of the records
has a NULL value
the Query hangs.

I have tried SELECT Client, Company, TheDate from MyTable WHERE TheDate >= :fromDate AND
TheDate<=:toDate AND TheDate IS NOT NULL

But it still happens. If I set the date range to records where I know that TheDate is not
NULL it works fine but as soon as TheDate has a NULL value
it hangs.

I need some records to have this NULL value and even when I include "TheDate IS NOT NULL"
it still hangs.

Am I doing something wrong? Thanks



OK - If I remove the parameters and replace with  WHERE TheDate >= Date'2001-01-01' AND
TheDate <=Date'2008-01-01'
Its works perfectly.
Why wont the parameter query work for me with this?
Thanks again
 

Dave


I don't know about the parameter part but if you want the null dates shouldn't it be an OR not an AND

Roy Lambert

Hi Roy,

I actually want to exclude NULL Dates, but was mainly doing it to try and get the
Parameter working.
The thing is I take out the Parameters and code in the Dates to the SQL and it works fine.
Using the Params with Dates, and it hits a NULL it just hangs.

Maybe it is just something I am doing wrong, but I am happy I have it working even though I
had to remove the Params, but I think using parameters is more professional.

Dave

Dave,

<< Works fine BUT when I am using a Parameter Date search like this, if one
of the records has a NULL value
the Query hangs. >>

What version of EDB are you using ?  There was an issue like this that was
resolved recently:

http://www.elevatesoft.com/scripts/incident.dll?action=viewrep&category=edb&release=1.06&type=f&incident=2493

The incident report describes accessing BLOB columns, but the issue could
have cropped up in other scenarios because the main cause was the parameters
and the insensitive (TEDBQuery.RequestSensitive:=False) result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I have the latest version, BUT I have recently discovered I havent cleaned up the library
paths before installing.
So this could mean that even though I have installed the latest version, the library path
if pointing to the wrong
version.

I might clean that up first.

Thank you for all your help

Dave,

<< So this could mean that even though I have installed the latest version,
the library path if pointing to the wrong
version. >>

Yep, that will do it.  I'll have to see about getting the uninstall to
remove the old paths.

--
Tim Young
Elevate Software
www.elevatesoft.com