>>I am creating a query and I want to filter records where the status is (released or completed) and the date is greater than 01/01/2006.
>>
>>It ignores the date filter. I have tried variations of ( x or y) and (z) parenthesis with no luck?
>>
>>fstatus is a character field
>>fopen_dt is a date field
>>
>>the status filter works fine, but the date does not.
>>
>>

SELECT Jomast.fjobno, Jomast.fpartno, Jomast.fsono, Jomast.fstatus,;
>>  Jopact.fjobno, Jopact.flabact, Jopact.fmatlact, Jopact.fovhdact,;
>>  Jomast.fopen_dt;
>> FROM ;
>>     M2MDATA!JOMAST ;
>>    INNER JOIN M2MDATA!JOPACT ;
>>   ON  Jomast.fjobno = Jopact.fjobno;
>> WHERE  Jomast.fstatus = ( "RELEASED" );
>>   OR   Jomast.fstatus = ( "COMPLETED" );
>>   AND  Jomast.fopen_dt > ctod("01/01/2006") ;
>> ORDER BY Jomast.fpartno

>>
>>I just noticed the FOPEN function in the field, hmmmm cause?
>
>
>When you have ANDs and ORs in WHERE clause you must very carefully decide where to put brackets.
>

>....
>WHERE  (Jomast.fstatus = ( "RELEASED" );
>   OR   Jomast.fstatus = ( "COMPLETED" ));
>   AND  Jomast.fopen_dt > DATE(2006, 1, 1) ;
>...
>

>
>or
>
>

>....
>WHERE  Jomast.fstatus IN ( "RELEASED", "COMPLETED" );
>   AND  Jomast.fopen_dt > DATE(2006, 1, 1) ;
>...
>

>
>And I prefer to use DATE() function to build dates, just because it not depends of the current SET DATE setting.

Good point about DATE function!

I wanted to change the date to '20060101' but stopped myself in the middle :) I did change double quotes to single quotes in the statuses, though :)