Thanks for the previous help.  I now have a data item defined as: CTOD(STRTRAN(TRANSACT.DATE,"/A","/0")).

That works perfectly in my report as far as display of the data.  It returns TR_DATE which has a data type of Character.  It may be more practical to have it as data type 'Date', but I cannot figure out how to change that.  The returned format when displayed in a report is 06/20/2009 which is very nice.

I need to filter on that data item.  I enabled filtering.  The format is given as @! But nowhere in the developer help, output formats, does it show what that means.  So I changed it to @D.  This did have result in that when I completed the filter field I had to enter a date as mm/dd/yyyy.  That is good.  But I got no results when looking for TR_DATE is after 06/01/2009. 

What do I modify.  I need this to work.  Usually I would want to run the report for sections of dates.  5/1/09 thru 5/31/09, 6/1/09 thru 6/30/09, 4/1/09 thru 6/30/09 etc.  The report works except that the filtering doesn't.

I pasted the SQL error below.  I have tried modifying data types, but that has not helped the situation.

Thanks,

Hans Manhave

Jackson-Lloyd

hmanhave@jackson-lloyd.com

SELECT        DISTINCT;

        PADR(UPPER(LTRIM(RTRIM(SUBSTR(DATA11,213,25)))),24) AS CH_MEMBER,;

        TRANSACT.TBAL,;

        TRANSACT.TYPE                  AS TYPE_T0,;

        PADR(CTOD(STRTRAN(TRANSACT.DATE,"/A","/0")),10) AS TR_DATE,;

        APCUSTM2.POL_IDX,;

        INS.NAME,;

        PADR(SUBSTR(APCUSTM2.POL_IDX,7,1),4) AS APCUSTM2_AGCY,;

        TRANSACT.ITEM,;

        TRANSACT.MONTH,;

        TRANSACT.TRANS,;

        TRANSACT.AMT                   AS AMT_T;

  FROM           "APCUSTM2" APCUSTM2;

                        INNER JOIN "TRANSACT" TRANSACT ;

                ON LEFT(APCUSTM2.POL_IDX,9) = LEFT(TRANSACT.POL_IDX,9) ;

                        INNER JOIN "POLICY" POLICY ;

                             INNER JOIN "INS" INS ;

                     ON LEFT(POLICY.POL_IDX,7) = INS.REC ;

                ON LEFT(APCUSTM2.POL_IDX,9) = LEFT(POLICY.POL_IDX,9) ;  INTO  CURSOR CHAMBERLIST;

  WHERE       (SUBSTR(APCUSTM2.POL_IDX,7,1)="7";

     AND UPPER(LTRIM(RTRIM(SUBSTR(DATA11,213,25))))<>"                        ";

     AND TRANSACT.BCO == "GEM";

     AND TRANSACT.TRANS IN ("NTO","TE1","TE3");

     AND TRANSACT.TBAL=0.00;

     AND CTOD(STRTRAN(TRANSACT.DATE,"/A","/0"))>"06/01/2009");

  ORDER BY  1

** (ORDER BY with field names)...

** ORDER BY            CH_MEMBER

I get a 'Operator/operand type mismatch' still.  The query runs for a while and then halts with that error.

I'm assuming that the error is caused by the SQL line:  CTOD(STRTRAN(TRANSACT.DATE,"/A","/0"))>06/01/2009)

I don't know if that last bracket is a close from the beginning of the SQL statement.

Does the CTOD function expect a date as 06/01/2009 or should it be 2009/06/01 or some other specification?