Query Record Number

[tobeon]

Hello

how could you make a query so all recordsa above a specific record number is displayed

(the actual record number not an autonumber field)

thank you for any help youcan give

p.s
this is quite urgent

 

[mdmilner]

The answer depends on how flexible you need the query to be for production use. If the number to test against changes, then you'll want to use VBA to define a query as needed.

I'm making an assumption that you're probably using something like a form and asking users to type in a value then hitting a command button to show your query.

Inserting this into the _Click() event...

Call MakeQry(Forms!formName.controlName)
DoCmd.OpenQuery qryName, acReadOnly

Function MakeQry(ByVal qryname As String, ByVal yourvalue As Long)
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs=CurrentDB()

strSQL = "SELECT * FROM tbl1 WHERE tbl1.fld1 > " & yourvalue & ";"

DoCmd.DeleteObject acQuery,qryName
Set qdf = dbs.CreateQueryDef(qryName,strSQL)

Set dbs = Nothing
End Function

This actually defines the query.
A simpler approach would be to avoid the need to delete/define a query
and use DoCmd.RunSQL

Function MakeQry(ByVal qryname As String, ByVal yourvalue As Long)
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs=CurrentDB()

strSQL = "SELECT * FROM tbl1 WHERE tbl1.fld1 > " & yourvalue & ";"

DoCmd.RunSQL strSQL

Set dbs = Nothing
End Function

The difference is, your needs. Do you need to use the query elsewhere?

Mike

 

[Russell Hauf]

You can also try a Top Values query. If you don't want to base it on a field in your table like you said in your request, just start a new query, and in the box (small drop-down) at the top (in the Query Design toolbar) where it says "All", just type in the number of records that you want to show.

HTH,

Russell