Do not include items where 8th & 9th char = PE or RE 1

[genekr]

I have a report where I do not want to include any item numbers where the 8th & 9th char's are PE or RE. CR9, PSQL9
Server 2003.

This is my formula:

(not ({IMITMFIL.ITEM_NO} startswith ["ALMISC", "ALSCRAP", "AWELD"])) and
{IMITMFIL.ITEM_NO} >= "A%" and
{IMITMFIL.ITEM_NO} <= "AZ%" and
(not ({IMITMFIL.ITEM_NO}[8 to 9] in "PE")) or (not ({IMITMFIL.ITEM_NO}[8 to 9] in "RE")) and
{@QtyNotSold} > 0.00 and
{IMITMFIL.ITEM_MFG_LOC} = "01"

Obviously what I'm trying is:

(not ({IMITMFIL.ITEM_NO}[8 to 9] in "PE")) or (not ({IMITMFIL.ITEM_NO}[8 to 9] in "RE"))

but it's returning all item numbers.

Thank you for any assistance.
Gene

 

[synapsevampire]

Try:

(
not({IMITMFIL.ITEM_NO} startswith "ALMISC")
and
not({IMITMFIL.ITEM_NO} startswith "ALSCRAP")
and
not({IMITMFIL.ITEM_NO} startswith "AWELD")
)
and
(
{IMITMFIL.ITEM_NO} >= "A%"
and
{IMITMFIL.ITEM_NO} <= "AZ%"
)
and
(
not ({IMITMFIL.ITEM_NO}[8 to 9] in ["PE","RE"))
)
and
(
{IMITMFIL.ITEM_MFG_LOC} = "01"
)

Then check the Database->Show SQL Query to see how it is being translated to SQL. We may need to tweak it a bit more, so if it still fails, post what's in there.

I fear some of this may not get passed to the database, and keep in mind that you can buold a substring function in a SQL Expression to get the 8-9th character from the field and then just use the record selection against the SQL Expression.

-k

 

[genekr]

Worked super. Thanks synapsevampire !