title: Access 2007 VBA Query Shows Data in Query Analyzer But Not in VBA Coded Recordset
body: <p>I have a function I've written that was initially supposed to take a string field and populate an excel spreadsheet with the values.  Those values continually came up null.  I started tracking it back to the recordset and found that despite the query being valid and running properly through the Access query analyzer the recordset was empty or had missing fields.</p>
  
  <p>To test the problem, I created a sub in which I created a query, opened a recordset, and then paged through the values (outputting them to a messagebox).  The most perplexing part of the problem seems to revolve around the "WHERE" clause of the query.  If I don't put a "WHERE" clause on the query, the recordset always has data and the values for "DESCRIPTION" are normal.</p>
  
  <p>If I put <em>anything</em> in for the WHERE clause the recordset comes back either totally empty (<code>rs.EOF = true</code>) or the Description field is totally blank where the other fields have values.  I want to stress again that if I debug.print the query, I can copy/paste it into the query analyzer and get a valid and returned values that I expect.</p>
  
  <p>I'd sure appreciate some help with this.  Thank you!</p>
  
  <pre><code>Private Sub NewTest()
  
  '  Dimension Variables
  '----------------------------------------------------------
  Dim rsNewTest As ADODB.Recordset
  Dim sqlNewTest As String
  Dim Counter As Integer
  
  '  Set variables
  '----------------------------------------------------------
  Set rsNewTest = New ADODB.Recordset
  
  sqlNewTest = "SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, " &amp; _
                  "dbo_part.partdescription as Description, dbo_partmtl.qtyper as [Qty Per] " &amp; _
              "FROM dbo_partmtl " &amp; _
              "LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum " &amp; _
              "WHERE dbo_partmtl.mtlpartnum=" &amp; Chr(34) &amp; "3C16470" &amp; Chr(34)
  
  '  Open recordset
  rsNewTest.Open sqlNewTest, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  
      Do Until rsNewTest.EOF
  
          For Counter = 0 To rsNewTest.Fields.Count - 1
              MsgBox rsNewTest.Fields(Counter).Name
          Next
  
          MsgBox rsNewTest.Fields("Description")
  
          rsNewTest.MoveNext
  
      Loop
  
  '  close the recordset
  
  rsNewTest.Close
  Set rsNewTest = Nothing
  
  End Sub
  </code></pre>
  
  <p>EDIT:  Someone requested that I post the DEBUG.PRINT of the query.  Here it is:</p>
  
  <pre><code>SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, dbo_part.partdescription as [Description], dbo_partmtl.qtyper as [Qty Per] FROM dbo_partmtl LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum WHERE dbo_partmtl.mtlpartnum='3C16470'
  </code></pre>
  
  <hr>
  
  <p>I have tried double and single quotes using ASCII characters and implicitly.</p>
  
  <p>For example:</p>
  
  <pre><code>"WHERE dbo_partmtl.mtlpartnum='3C16470'"
  </code></pre>
  
  <p>I even tried your suggestion with chr(39):</p>
  
  <pre><code>"WHERE dbo_partmtl.mtlpartnum=" &amp; Chr(39) &amp; "3C16470" &amp; Chr(39)
  </code></pre>
  
  <p>Both return a null value for description.  However, if I debug.print the query and paste it into the Access query analyzer, it displays just fine.  Again (as a side note), if I do a LIKE statement in the WHERE clause, it will give me a completely empty recordset.  Something is really wonky here.</p>
  
  <hr>
  
  <p>Here is an interesting tidbit.  The tables are linked to a <code>SQL Server</code>.  If I copy the tables (data and structure) locally, the ADO code above worked flawlessly.  If I use DAO it works fine.  I've tried this code on <code>Windows XP</code>, <code>Access 2003</code>, and various versions of <code>ADO (2.5, 2.6, 2.8)</code>.  <code>ADO</code> will not work if the tables are linked.</p>
  
  <p>There is some flaw in ADO that causes the issue.</p>
  
  <hr>
  
  <p>Absolutely I do.  Remember, the <code>DEBUG.PRINT</code> query you see runs perfectly in the query analyzer.  It returns the following:</p>
  
  <pre>
  Job/Sub     Rev         Description                     Qty Per
  36511C01     A          MAIN ELECTRICAL ENCLOSURE       1
  36515C0V     A          VISION SYSTEM                   1
  36529C01     A          MAIN ELECTRICAL ENCLOSURE       1
  </pre>
  
  <p>However, the same query returns empty values for Description (everything else is the same) when run through the recordset (messagebox errors because of "Null" value).</p>
  
  <hr>
  
  <p>I tried renaming the "description" field to "testdep", but it's still empty.  The only way to make it display data is to remove the WHERE section of the query.  I'm starting to believe this is a problem with ADO.  Maybe I'll rewriting it with DAO and seeing what results i get.</p>
  
  <p>EDIT:  I also tried compacting and repairing a couple of times.  No dice.</p>