What is the best way to approach record selection

[Jon]

In a table I have 100 records or more, some times we need to work out the
standard Dev with a different base line and print out charts you show the
results.

My question is what is the best way of selecting the appropriate records
e.g. query, VB or SQL as some times we need the first 10 records in the
table, all the records, the first 10 of the last 20 records or a certain
range selected by someone.

Thanks in advance Jon

 

[Brendan Reynolds]

It sounds to me like you're going to need a combination of VBA and SQL - a
dynamic SQL string to select the records, and VBA to dynamically build the
SQL string according to the options selected by the user. The details would
depend on a) the data and b) the method used to capture the user's
selections, but assuming that the user enters their selections in one form,
then clicks a button to display the results in another form, the code in the
second form's Open event procedure might look something like this ...

Dim strSQL As String

strSQL = "SELECT TOP " & _
Forms!FirstForm!NumberOfRecordsControl & _
" FROM SomeTable WHERE SomeField BETWEEN " & _
Forms!FirstForm!Criteria1Control _
& " AND " & _
Forms!FirstForm!Criteria2Control & _
" ORDER BY " & _
Forms!FirstForm!Criteria2Control

If Forms!FirstForm!DescendingCheckBox = True Then
strSQL = strSQL & " DESC"
End If

Me.RecordSource = strSQL