one SQL statements gives two different results???

[Vie]

I have a strange problem and I don't know where to begin to fix it. I have a form with a listbox on it (frm1). The user selects whatever items from the listbox they want and then clicks a button which opens another form (frm2) and automatically populates a subform on frm2 with the selection they made in the listbox. The problem is the SQL string that is built on their selection returns the wrong recordset (no error message or other problems along the way).

So I took the strSQL out of the module and copied it to a new query in SQL view and figured I'd tinker around with it until it returned the correct recordset. Strangely, when I run the query, the correct recordset is returned even though I didn't change anything about it.

Now I've copied the string back and forth a few times from code to query and vice versa without changing anything (except removing, obviously, the quotes and line continuation characters). The result is always the same. The wrong recordset in the subform and the correct recordset in the query.

At run-time in the forms, I have a msgbox popup to show me SQL string and I can find nothing wrong with it. It is exactly the same as in the query.

How can one SQL statement produce two different results? Can anyone help me with this? This is utterly confounding.

If it is of any consequence, I am using Access 2002.

Below, the strSQL and the associated code:

strTenantSelection = "SELECT qryTenantsApartments.TenantID, " & _
"qryTenantsApartments.LastName, qryTenantsApartments.FirstName, " & _
"qryTenantsApartments.Name, qryTenantsApartments.Apt, " & _
"qryTenantsApartments.[Voucher Status], " & _
"qryTenantsApartments.ProjectID, qryTenantsApartments.Floor, " & _
"qryTenantsApartments.Apartment, qryTenantsApartments.ProjectName, " & _
"qryTenantsApartments.Comments, qryTenantsApartments.Eligible, " & _
"qryTenantsApartments.ApplicationDate, qryTenantsApartments." & _
"BriefingAttendedDate, qryTenantsApartments.VoucherIssuedDate, " & _
"qryTenantsApartments.Address, qryTenantsApartments.ZipCode, " & _
"qryZipCode.City, qryZipCode.StateAbb, tblDates.NoticeID, " & _
"tblDates.DeliveryDate " & _
"FROM (qryTenantsApartments INNER JOIN (qryZipCode INNER JOIN " & _
"tblProject ON qryZipCode.ZipCode = tblProject.ZipCode) ON " & _
"qryTenantsApartments.ProjectID = tblProject.ProjectID) LEFT JOIN " & _
"tblDates ON qryTenantsApartments.TenantID = tblDates.TenantID " & _
"WHERE (((qryTenantsApartments.TenantID) In ("

For Each varTenantID In ctl.ItemsSelected
strTenantSelection = strTenantSelection & ctl.ItemData(varTenantID) & ", "
Next varTenantID

strTenantSelection = Left(strTenantSelection, Len(strTenantSelection) - 2)

strTenantSelection = strTenantSelection & &quot));"

 

[vbajock]

Instead of trying to deconstruct the string, I would put a stop in my code right after the sql statement is built. When it hits the stop, go into the Immediate window type
?strTenantSelection, hit enter. It will put the actual Sql in the window. Cut and paste it into a query window and run it. Review the statement and make sure it is interpetting it the way you think it is.