This statement works perfectly:

Select * from BillItemLine Inner Join Item on BillItemLine.ItemLineItemRefListID = Item.ListID Where BillItemLine.IsPaid = False

This statement does not work:

Select BillItemLine.ItemLineItemRefListID, Item.ListID, BillItemLine.IsPaid 
from BillItemLine Inner Join Item on BillItemLine.ItemLineItemRefListID = Item.ListID Where BillItemLine.IsPaid = False

In the Item.ListID column it always returns same value.  It is the value of the last record in BillItemLine, rather than the corresponding value in the Item table.

As far as I can tell, the SELECT statements are the same except I am naming a list of value in the second one.

Thanks.

Sorry you've lost me on this one. The SQL Statement requires BillItemLine.ItemLineItemRefListID to always equal the Item.ListID.

Sorry, subsequent to posting I realized the problem is in ADO, not in the Query and I couldn't edit the post because it requires approval.

I ran the two queries using the VB-Demo and got the same result for each, so the query syntax is correct.

The problem is that the following ADO code fails to return a correct result to the recordset using the following recordset open statement:

rs.Open vSQL, cn, adOpenStatic, adLockOptimistic

The result is that the second field Item.ListID contains a constant value in every record (which happens to be the value of the last record).  This only occurs if I name fields in the SELECT.  If I use SELECT * from <rest of query> then Item.ListID appears correctly.

but the second query does work with a dynamic recordset opened as follows

rs.Open vSQL, cn

The problem with this is that when you open a recordset this way, it will not give you a recordcount.  Always returns -1.

I ended up working around by using DO WHILE NOT RS.EOF and counting the records manually, but I'm concerned that there are conditions that will return incorrect results which could cause me some future confusion/errors. 

Is this just an attribute of using ADO with QODBC or am I coding incorrectly?

Sub TestSpecial1()

'Opens connection if not already open in Global var

If CNisOpen <> True Then
    Call InvokeQODBC
    Debug.Print Now(), "Called Invoke QODBC"
End If

Dim rs As ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")

'Returns incorrect results in Item.ListID with adOpenStatic, works only if open adOpenDynamic
vSQL = "Select BillItemLine.ItemLineItemRefListID, Item.ListID from " BillItemLine Inner Join Item on BillItemLine.ItemLineItemRefListID = Item.ListID " & "Where BillItemLine.IsPaid = False"
rs.Open vSQL, cn, adOpenStatic, adLockOptimistic

ActiveSheet.Cells.ClearContents
For iCols = 0 To rs.Fields.Count - 1
    ActiveSheet.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next

rs.MoveFirst
ActiveSheet.Range("a2").CopyFromRecordset rs

Debug.Print Now(), "Query Complete"

rs.Close
Set rs = Nothing

End Sub

Thanks. Your suggestion does solve another problem for me, potentially, regarding query performance.

But, this question is about the SQL sent through ADO returning an incorrect result.

In the SQL statement in my example, "Select * from <rest of statement> works with open adOpenStatic or adOpenDynamic.

But, when I name fields "Select <field1>, <field2> <rest of statement", where <field2> is in the joined table, I get incorrect results with adOpenStatic, but correct results with adOpenDynamic.  The incorrect results are that a constant value is returned (always the value of the last record.

adOpenDynamic, however, will not return a recordcount.

I can get around this, but just wondered if you had seen this before and whether you had a solution other than opening the recordset differently.

It does not have to do with the FILL vs. CopyRecordset, purely with the recordset.OPEN.  I guess that is where I could try the data adapter, if that is what you are recommending.

At a rough guess I would say it's your INNER JOIN that's the problem, try:

vSQL = "Select BillItemLine.ItemLineItemRefListID, Item.ListID from BillItemLine,  Item where BillItemLine.ItemLineItemRefListID = Item.ListID " &
              "and BillItemLine.IsPaid = False"
rs.Open vSQL, cn, adOpenStatic, adLockOptimistic

Yes, that solved the problem.

Thanks.