Retrieving Record from Recordset using criteria

[iheartvba]

Hi Guys,
thanks for the great work you are doing

I am working on a database in MS Access and am trying to bring up the last
receipt number in the recordset (its the primary key of the recordset) but am running into this problem, every year the range of the receipts will be different e.g.
2005 it could be 80000 - 90000 while in
2006 it could be 10000 - 20000
meaning that the .MoveLast function would
bring back the last receipt number for 2005. What can I use to get only the range of receipt numbers that I want
here is a sample of my code

Public rst As New ADODB.Recordset
Dim lngNS4Reciepts as Long
Select Case tbOfficeID 'text box in form
Case 4 'for office number 4
  'tblJobDetailsNS4 is table with RcptID as primary key for office 4rst.Open "tblJobDetailsNS4", cnn, adOpenKeyset, adLockOptimistic    With rst          .MoveLast           lngNS4Reciepts = !RcptID (primary key of tblJobDetailsNS4)    End With'This gives me incremental receipt numbers tbRcptID = lngNS4Reciepts + 1  Set rst = Nothing 

Thanks alot

[MMcCarthy]

You need to create a query to limit the returned records and open the query instead of the table.

[iheartvba]

You need to create a query to limit the returned records and open the query instead of the table.

yeah thats what I thought as well, but thats the llooooong way, first I would Interogate the table with a query then, I would have to run an append query to put the data into the table. I was thinking along the lines of instead of moveto.first or moveto.last just say moveto.(number 4****)

[pks00]

What about this

rst.find "somefield = somevalue", , adSearchBackward