filtering a recordset

[tyedyejenn]

I am trying to Select only records that match an account number.&nbsp;&nbsp;Here is the code I have, it selects all the records from the table but I can't figure out how to select only the record I want <br>Please help--Thanks Jenn<br><br>Dim cnn1 As ADODB.Connection<br>Dim cmdAccountCheck As ADODB.Command<br>Dim rsAccountCheck As ADODB.Recordset<br><br>Dim strCnn As String<br><br>'open the connection<br>Set cnn1 = New ADODB.Connection<br><br>strCnn = &quot;Provider=MSDASQL.1;Password=hrrs;Persist Security Info=True;User ID=Adm;Data Source=EricsDrive&quot;<br><br>cnn1.Open strCnn<br><br>Set cmdAccountCheck = New ADODB.Command<br><br>Set cmdAccountCheck.ActiveConnection = cnn1<br><br>'cmdAccountCheck.CommandText = &quot;SELECT Customer_File.[Nam], Customer_File.[Phone1] From Customer_File WHERE Customer_File.[CustNo]=&quot; & &quot;'&quot; & strCasAcctNumber & &quot;'&quot; & &quot;&quot;<br><br>'cmdAccountCheck.CommandType = adCmdStoredProc<br><br><br>Set rsAccountCheck = New ADODB.Recordset<br><br><br>rsAccountCheck.Open &quot;Customer_File&quot;, cnn1, adOpenKeyset, adLockOptimistic<br><br>When I uncomment the cmdAccountCheck.CommandText I get errors, I am new to ADO so any help is greatly appreciated<br>Thanks Again--Jenn

 

[dellyjm]

Try rs.Find &quot;Last_Name = 'Clovis'&quot; <br>or <br>rs.Filter = &quot;AccountBalance &gt; 1000&quot;<br><br>After you're done filtering you can set the recordset back to it's original state by doing the following<br><br>rs.filter = adFilterNone<br><br>Account balance being the field name.<br>where rs is the recordset and last_name is the table field name. Try it.<br><br>Hope it helps.

 

[positran]

What kind of error are you getting?<br><br>You should be able to shorten the SQL to this:<br><br>&quot;SELECT Nam, Phone1<br>From Customer_File<br>WHERE CustNo = '&quot; & strCasAcctNumber & &quot;';&quot;<br><br>Also make sure that the 'strCasAcctNumber' variable matches the variable type in the DB.<br><br>