Error in searching criteria Code!!!

[hyousef]

I have a data block contains some text boxes (sno, empno, projectno, startdate), currently, users can click the "Execute" button and return all records or users can go to Enter query mode and insert a value (search criteria) into any of these textboxes, and then they will get the required record after clicking the "Execute" button.

I added 2 non database text boxes(date1,date2) for searching purpose, it will return all startdates located between these 2 dates. I used the following code for that and its working fine but "IT DISABLED THE OTHER SEARCHING CRITERIA (sno, empno...)" which is not good because i want a suer to have a choice.

Declare
where_dt varchar2(150);
Begin
IF :System.Mode = 'NORMAL' THEN
set_block_property('blk_name', default_where,'');
Else
where_dt:='startdate between :blk_name.date1 and :blk_name.date2';
set_block_property('blk_name', default_where,where_dt);
End if;
Execute_query;
End;


So, i edited the code to include the other criteria, users can search by one criteria only.

Declare
where_dt varchar2(150);
Begin
IF :System.Mode = 'NORMAL' THEN
set_block_property('blk_name', default_where,'');

Else if :System.Mode = 'Query' Then

if :blk_name.Date1 and :blk_name.Date2 is not null then
where_dt:='startdate between :blk_name.date1 and :blk_name.date2';
end if;

if :blk_name.sno is not null then
where_dt:='sno = :blk_name.sno';
end if;

if :blk_name.empno is not null then
where_dt:='empno = :blk_name.empno';
end if;

set_block_property('blk_name', default_where,where_dt);
End if;
End if;

Execute_query;
End;

Unfortunately, this code is not working, i am getting error.
It points to the first "if :blk_name.Date1" and returns error.
Error 382.
Expression is of wrong type.


I can't find an error!!!

Any help please??

[mjphillips]

Morning,

Your error is caused by the test below due to the comparison
between ":blk_name.Date1 and :blk_name.Date2"

> if :blk_name.Date1 and :blk_name.Date2 is not null then

A possible solution would be:

if (:blk_name.Date1 is not null) and (:blk_name.Date2 is not null) then ...


Hope this helps.

[hyousef]

Thank you mjphillips,

It works.