Alternate to Not In

Replace IN with EXISTS. It's more accurate anyway. Other than some NULL and empty differences, both do the same.

Cluskitt,

I tried your suggestion. It did not work even in MS Access query. I got "Syntax error".

Note that the logic is NOT IN so I tried NOT Exists

Yes, that's how it should be. Unless you're using older versions of access.

I need a solution that works in adodb and neither "IN" or "EXISTS" will work there. I have seen several sample solutions using LEFT JOIN, but for very simple situations where there are no table joins. I can't seem to "adapt" those samples to this more complex need.

This should do it.
SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  LEFT OUTER JOIN
      (
            SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID)
            WHERE tZoneList.ZoneID <> ‘ParameterName01’
      ) C1 ON tCOHeader.CONumber = C1.CONumber
WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate

lludden,

I think you are on the right track; but I got a Syntax error. See attached document.

Thanks

 Doc5.doc

lludden,

I saw that too, but removing it does not resolve the issue. I have tried to enclose other phrases in parenthesis with no luck. Note that my original SQL ran in MS Access, (but will not run in ado) so I know the table and fields names are accurate.  I need something that will run in ado, but I am trying your solution first in Access to make sure it works there....and so far no luck.

you return the 2nd recordset as alias C1; should the first record set also be return as an alias?

Could you take a close look at it again?

thanks

Frank

Due to your problems so far, I have to say I'm more inclined to think that your problem lies in the adodb syntax, rather than SQL. As I said, if you have a query that worked in Access, it has to work when you use adodb to connect to the same database. If it doesn't, the problem lies in the connection string (or similar), not in the SQL query.

You should post your file(s) so we can test and see why it isn't working. If your file(s) contain data you don't wish others to see, replace it with sample dummy data of the same kind.

Cluskitt and lludden,

Here is the database where I am testing the query.

thanks for all your help.

ExEx.mdb

Ok, from what I've been testing, qOneZone works well enough. Now, the problem you might be having with adodb is the parameter part. The query itself is sound.
I've had a similar problem when trying to merge access and word. VBA doesn't like parameter queries. What you should do is create a connection to the database, then dynamically change the SQL of the query, replacing the parameter with some value, for example, from a variable.

I am assuming that you're trying this with VB6/VBA. If you're trying a manual connection, use DDE instead.

Cluskitt;

Been there; done that. That is where I started.

I should have included the query that lludden proposed. Copy that, plug it into Access and you will see it does not work (even after removing the extra parenthesis).

Frank

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  LEFT OUTER JOIN
      (
            SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID
            WHERE tZoneList.ZoneID <> ‘ParameterName01’
      ) C1 ON tCOHeader.CONumber = C1.CONumber
WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate

The thing is: there is no problem with using IN. If you change it to JOINS instead, you'll have the exact same problem, because you'll still have a parameter.

However, if you want the same thing but with JOINS, you'll have to try something like this:

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  INNER JOIN
      (SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS ON tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID
            WHERE tZoneList.ZoneID <> ‘ParameterName01’) C1
       ON tCOHeader.CONumber <> C1.CONumber
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate

Cluskitt,

Did you test the query you provided into the database I uploaded? I just plugged it into MS Access here and it gives me a Syntax error (missing operator).

Frank

Sorry, there was a slight problem with it. It would work fine on SQL server, but access is pickier. This was tested and works:

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader INNER JOIN (SELECT DISTINCT tCOHeader.CONumber FROM (((tCOHeader LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber) LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot) LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID) WHERE tZoneList.ZoneID <> ‘ParameterName01’) AS C1 ON tCOHeader.CONumber=C1.CONumber
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate;

Cluskitt,

That query works fine in Access (Thank you), but it (like the one with the NOT IN command) will not work in my use of ado in VB6. I DO replace the parameter with a string value. I use the same code to process other selection statements and they work fine so I think my connection etc. is right. I get an error that says "No vaue given for one or more required parameters". Does that trigger any suggestions for you?

Thanks again. I am pulling my hair out.

Frank

When parsing the string, anything NOT recognized as a table field will be considered a parameter. So if you have a field named say "ImportedDate" and by mistake you type "ImportDate", it will NOT match any field and will complain that you don't have a value for the PARAMETER "ImportDate". So doublecheck EVERY SINGLE field name in your SQL for differences. If the query works in Access, it ought to be fine, but only if you're connecting to the same DB, same user, etc. Doublecheck all those too :-)

Akenathon,

To test this I am copying and pasting the whole selection string from the query in Access to a text field in a database table where the code picks it up and replaces the Parameter and then uses the modified string to (try to) open a recordset. So the field names are the same. When executed as an Access Query it prompts me for one parameter as it should. I replace that parameter before using it to open the recordset in the code.

Same database. Same User. Works for various other select statements. Everything looks like it should work. But it doesn't (yet)

Thanks for the suggestions.

Frank

By the way the following is the exact sql select statement that works fine in Access and gives me exactly the results I need:

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader LEFT JOIN (SELECT DISTINCT tCOHeader.CONumber FROM (((tCOHeader LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber) LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot) LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID) WHERE tZoneList.ZoneID <> ‘ParameterName01’) AS C1 ON tCOHeader.CONumber=C1.CONumber WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate

Attached is the selection string just before I use it to open the recordset as follows:

ssSelectorParameters.Open sSql, gsConn, adOpenForwardOnly, adLockReadOnly, adCmdText

And the gsConn Connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gsFishbone & ";Persist Security Info=False;JET OLEDB:Database Password=XXXXXXXXXXXXX"

Where gsFishone is the path to the database. SQL.txt

Resolved. I will explain and assign points on Monday.

thanks

Cluskitt was awarded for his dogged insistence that the same SQL should work in Access and ado. LLudden was awarded for providing the solution based on LEFT JOINS that I asked for. The problem which we all missed was that I had used a "left quote" for the delimiting the text. It worked in Access but not in ado. Go figure.

Thanks. I love this service.