>The following SPT string was suggested a while back and has worked fine until today. Today it hit a part number taht was inspected twice on the same day in which one record shows rejected the other accepted. In this case I only need the accepted returned. What do I need to do to make this happen?
>
>testString = "Select recseq, CASE WHEN faistatus = 1" +;
>" THEN 'Accepted' WHEN faistatus = 2 THEN 'Rejected' ELSE 'SEE ORIGINAL FAI' END" +;
>" As fais, inspectord, dwgrev From webprddt6.drawmfai mt1" +;
>" JOIN ( SELECT webprddt6.drawmfai.partno, MAX(webprddt6.drawmfai.inspectord) AS MaxDate FROM webprddt6.drawmfai GROUP BY webprddt6.drawmfai.partno, webprddt6.drawmfai.vendorcode ) mt3" +;
>" ON mt3.partno = mt1.partno AND mt3.MaxDate = mt1.inspectord" +;
>" Where (mt1.partno = '116405-015' Or mt1.partno = '116405-015') And Left(mt1.vendorcode,5) = 'G7135'"

Steven,
Please next time format your code. It is hard to read and understand (at least for me)
Also why not use TEXT ENDTEXT block when you build such statements. It will be easier for you also:

TEXT TO testString NOSHOW TEXTMERGE PRETEXT 15
     Select recseq, CASE WHEN faistatus = 1
                              THEN 'Accepted'
                         WHEN faistatus = 2
                              THEN 'Rejected'
                    ELSE 'SEE ORIGINAL FAI' END As fais,
            inspectord, dwgrev
     FROM webprddt6.drawmfai mt1
     INNER JOIN (SELECT webprddt6.drawmfai.partno, MAX(webprddt6.drawmfai.inspectord) AS MaxDate
                    FROM webprddt6.drawmfai
                 GROUP BY webprddt6.drawmfai.partno,
                          webprddt6.drawmfai.vendorcode ) mt3
            ON mt3.partno = mt1.partno AND mt3.MaxDate = mt1.inspectord
      WHERE (mt1.partno = '116405-015' Or mt1.partno = '116405-015') And Left(mt1.vendorcode,5) = 'G7135'
ENDTEXT

I have questions here. Is inspectord is datetime?
And if it is datetime what you store there? Only date part of full datetime?
Also what you want as a result for these two situations:

1. The record is first rejected and after that accepted?
2. The record is first accepted and after that rejected?


O, BTW is this a typo here:
mt1.partno = '116405-015' Or mt1.partno = '116405-015'
Both partnos are equal, then why you need OR?