Only records 250001-500000

[nlbertram]

Hello--

Please see my current code below. It currently runs the DoWhile loop for ID=1 until ID=250000.

I would like to run the DoWhile loop starting at ID=250001 and ending at ID=500000. This needs to be done without altering the SQL.

Any ideas?



Public Sub fixData()
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngNumber As String

strSql = "Select * from emp_id order by [ID]"
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While rs![ID] < 250000
If IsNull(rs![dot_one]) Then
rs.Edit
rs![dot_one] = lngNumber
rs.Update
Else
lngNumber = rs![dot_one]
End If
rs.MoveNext
Loop
End Sub

 

[PHV]

This needs to be done without altering the SQL
???

strSql = "SELECT * FROM emp_id WHERE ID BETWEEN 250001 And 500000 ORDER BY ID"
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rs.EOF
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya nlbertram . . .

[blue]I would like to run the DoWhile loop starting at ID=250001 and ending at ID=500000. [purple]This needs to be done without altering the SQL.[/purple][/blue]

The easiest thing to do would be to [blue]modify the SQL[/blue] ... as shown by [blue]PHV[/blue]. However if you must, here's your code without touching the SQL:

[blue]Public Sub fixData()
   Dim [purple][b]db As DAO.Database[/b][/purple], rs As DAO.Recordset, strSql As String, lngNumber As String
   
   [purple][b]Set db = CurrentDb[/b][/purple]
   strSql = "Select * from emp_id order by [ID]"
   Set rs = [purple][b]db[/b][/purple].OpenRecordset(strSql, dbOpenDynaset)
   
   [purple][b]rs.FindFirst "[ID] = 250001"[/b][/purple]
   
   Do While rs![id] < [purple][b]500001[/b][/purple]
      If IsNull(rs![dot_one]) Then
        rs.Edit
        rs![dot_one] = lngNumber
        rs.Update
      Else
        lngNumber = rs![dot_one]
      End If
      
      rs.MoveNext
   Loop
   
   Set rs = Nothing

End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

To explain why it is better to alter the SQL statement, you might currently be selecting many more records than you actually need. This could significantly slow down your performance, if you are for example retrieving 10 times the number of records you actually need.

 

[nlbertram]

Lots of good advice.

A special thanks to TheAceMan1