I have a routine that checks the validity of some items in a transaction against some values in a database.  I need to add a new variable to check against.    I want to check the transaction rows that are selected to see if any match the value IMITEM.Webaccessibility with having the value ‘A’ or ‘S’.    In the example below I added the column to the query so it is selected, but what is the best way to check the compared column to make sure none are set to N or S?  *The values are ‘N’, ‘C’, ‘S’ or ‘A’.     I added

(IMItem.WebAccessibility Not In (“N”,”S”)

to the cfif query that will loop the results… but I don’t think I have this correct.   What is the correct way to handle a NOT In.   (OR should it be IN? )  I know if it was a single value NEQ would be it.  But I need to compare 2 values

Code:

<cfcase value=”Verify”>
<!— Check if there are any items, which are not available and remove them —>
<cfquery name=”qryTransHeader”
datasource=”#Application.dsn#”>
SELECT OMTransactionHeader.ShippingDate,
IsNull(OMTransactionDP.StatusCheck_Pending,’E’) As CheckOrderedQnty
FROM OMTransactionHeader
LEFT OUTER JOIN OMTransactionDP ON OMTransactionHeader.R_Profile = OMTransactionDP.RowID
WHERE OMTransactionHeader.RowID = ‘#attributes.TransRowID#’
</cfquery>

<cfquery name=”qryDetailsStatus” datasource=”#Application.dsn#”>
SELECT IMItem.RowID,
IMItem.ItemCode,
(IsNull(IMItem.DESCRIPTION_1,”) + ‘ ‘ + IsNull(IMItem.DESCRIPTION_2,”)) AS Description_1 ,
IMItem.Status,
IMItem.WebAccessibility,
IsNull(IMItem.NetAvailable,0) AS NetAvailable,
IMItem.DateAvailable,
IMItem.DateExpiration,
IsNull(OMTransactionDetail.QuantityOrdered,0) AS QuantityOrdered
FROM OMTransactionDetail
LEFT OUTER JOIN VW_OMWEBITEMVIEW AS IMItem WITH (NOLOCK) ON IMItem.RowID = OMTransactionDetail.R_Item
WHERE (OMTransactionDetail.R_TransactionHeader = ‘#attributes.TransRowID#’)
</cfquery>

<cfset NonAvailableItems = “”>

<cfloop query=”qryDetailsStatus”>
<cfif ((application.FilterByStatus EQ “Y”) AND (Status NEQ “A”) AND (IMItem.WebAccessibility Not In (“N”,”S”) ) ) OR
((application.FilterByNetAvailable EQ “Y”) AND (NetAvailable LTE 0)) OR
((len(DateAvailable) NEQ 0) AND (DateCompare(ParseDateTime(#DateFormat(DateAvailable,”mm/dd/yyyy”)# & ” 00:00:00″),qryTransHeader.ShippingDate) GT 0)) OR
((len(DateExpiration) NEQ 0) AND (DateCompare(ParseDateTime(#DateFormat(DateExpiration,”mm/dd/yyyy”)# & ” 23:59:59″),qryTransHeader.ShippingDate) LT 0)) OR
((application.QuantityCheck EQ “P”) AND (qryTransHeader.CheckOrderedQnty NEQ “E”) AND (NetAvailable LT QuantityOrdered)) OR
((application.QuantityCheck EQ “Y”) AND (NetAvailable LT QuantityOrdered))>
<cfset NonAvailableItems = ListAppend(NonAvailableItems,RowID)>
</cfif>

</cfloop>