Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

genekr

Technical User
Jul 19, 2005
45
US
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


 
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
 
Worked super. Thanks synapsevampire !
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top