Help with Formula please

[icw1]

Hi,
I often have a problem Excluding records in Crystal as I am not sure of the syntax,
here is a SQL query that does exactly as i want. Can you tell me how to achieve the same record selection criteria in Crystal...many thanks


SELECT "OPMGR"."OPID", "OPMGR"."RECTYPE", "OPMGR"."COMPANY", "OPMGR"."STATUS", "OPMGR"."STARTDATE", "OPMGR"."CLOSEBY", "OPMGR"."STAGE"
FROM "GMSM_GMBASE"."dbo"."OPMGR" "OPMGR"
WHERE "OPMGR"."RECTYPE"='PK' AND ("OPMGR"."CLOSEBY">={ts '2007-01-01 00:00:00'} AND "OPMGR"."CLOSEBY"<{ts '2009-01-02 00:00:00'}) AND "OPMGR"."OPID" NOT IN (SELECT "OPMGR"."OPID" FROM "GMSM_GMBASE"."dbo"."OPMGR" "OPMGR" WHERE "OPMGR"."STAGE" = '(Templates)')

[Madhi]

Cant you use selection formula?

[Lugh]

Use the Select Expert.

For the first criteria ("OPMGR"."RECTYPE"='PK'), this is pretty simple. Select RECTYPE as the field. Select "is equal to" as the relationship. Enter PK as the value.

For the second criteria ("OPMGR"."CLOSEBY">={ts '2007-01-01 00:00:00'} AND "OPMGR"."CLOSEBY"<{ts '2009-01-02 00:00:00'}), select CLOSEBY as the field. Select "is between" as the relationship. Enter 2007-01-01 as the first value, and 2009-01-02 as the second value.

As for your third criteria, that's a little trickier. Well, your specific example isn't (incidentally, why isn't this just "STAGE <> '(Templates)'?). If you want to reference a subquery, there are two options. If it is a subquery of the current dataset, as yours is, you can create a SQL Expression, and use that in your formula (though, in general, I wouldn't recommend this). Otherwise, I would simply add the subquery to the SQL itself, either in the database if that's an option, or by using the Add Command function when setting up the data.