SQL Statement With Several Logical Operators

Question

I need to run a SQL query where -current_queue does not equal i01 and does not start with F -prod_code starts with R

SQL = "SELECT " & Join(theseFields, ",") & " FROM [Acct Detail$] WHERE NOT (current_queue='I01' OR LEFT(current_queue,1)='F')"

That works for me to remove i01 and queues starting with F but now how do I filter out prod_code that doesn't start with R?

(this is ADODB, VBA, Excel, Microsoft.Jet.OLEDB.4.0)

Answer 1

Nevermind, I decided to go with this:

SQL = "SELECT " & Join(theseFields, ",") & " FROM [Acct Detail$] WHERE NOT (current_queue='I01' OR LEFT(current_queue,1)='F' OR LEFT(prod_code,1)='C' OR LEFT(prod_code,1)='P')"

Still curious how to keep prod_code starting with R but not current_queue of i01 or starting with F

Answer 2

Split into lines for clarity, these should be on one single line

SQL = "SELECT " & Join(theseFields, ",") & " FROM [Acct Detail$]
WHERE LEFT(Prod_code,1) = 'R'
AND NOT (current_queue='I01' OR LEFT(current_queue,1)='F')"

Answer 3

You can run into case sensitivity with Excel :

strSQL = "SELECT " & Join(theseFields, ",") & " FROM [Acct Detail$] " _
& "WHERE (Not (UCase(current_queue)='I01' " _
& "Or UCase(current_queue) Like 'F%')) " _
& " AND UCase(prod_code) Like 'R%' "

If this were Access you would also have to check for Null, but it does not seem to be a problem with Excel.