Retrieve top N values through criteria

[epardo87]

Hello all,

• I have a form with a textbox, where they can select how many records to see on the query (5,10,15 or 20)
• Among the query columns, one is "Due Date"

how can I tell the query through the criteria field to show me the top (selected) records, sorted by earliest "Due Date"?

[ranman256]

create a query ,say: qsResults
now the user will pick items on the form, and press a GO button.
now when the user makes choices on the form, the code will reWrite the query for the TOP n...

sub btnGo_click()
dim qdf as querydef
dim sSql as string

if IsNull(me.cboTop) then
  sSql = "SELECT * FROM tData WHERE [Due Date]=forms!myForm!txtDate
else
  sSql = "SELECT TOP " &  me.cboTop &  " tData.* FROM tData WHERE [Due Date]=forms!myForm!txtDate
endif

     'save the sql as a qry or open the sql

set qdf = currentdb.querdefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name
end sub

[aytee111]

See Top N Values by Group: http://allenbrowne.com/subquery-01.html

[epardo87]

create a query ,say: qsResultsnow the user will pick items on the form, and press a GO button.now when the user makes choices on the form, the code will reWrite the query for the TOP n...

sub btnGo_click()dim qdf as querydefdim sSql as stringif IsNull(me.cboTop) then  sSql = "SELECT * FROM tData WHERE [Due Date]=forms!myForm!txtDateelse  sSql = "SELECT TOP " &  me.cboTop &  " tData.* FROM tData WHERE [Due Date]=forms!myForm!txtDateendif     'save the sql as a qry or open the sqlset qdf = currentdb.querdefs("qsResults")qdf.sql = ssqlqdf.closedocmd.openquery qdf.nameend sub

Thanks for your answer,

Lets say it is a little more complicated than what I exposed, Let me tell you the complete thing this is supposed to do:

Form frmExpiring

• Combobox cboWeeks (has values: 6;5;4;3;2;1;0) this will show results within the next N weeks ("Due Date" column in query)
  
• Combobox cboCerts (has values: 20;15;10;5;0) this will show the top N results with earliest dates ("Due Date" column in query)

• Option opWeeks (enables cboWeeks and disables cboCerts)
  
• Option opCerts (enables cboCerts and disables cboWeeks)

• Buttons btnOK opens a form with an embedded report made with the query qryExpiring

I already have everything working fine, the ok button already opens the form with the report of the unfiltered query embedded....

I was building a formula in the criteria field but it seems it doesn't work...

Like IIf([forms]![frmExpiring]![lstWeeks]>0,Between Now() And (Now()+[forms]![frmExpiring]![lstWeeks]),"*")

tough it is not filtering the dates correctly and I was thinking on replacing the last "*" for a code filtering the top records...

[ranman256]

Date math is done using
DateAdd("d",6,[datefield])

[ssanfu]

It looks like you are using VBA - but there are a couple of errors.

Sub btnGo_click()
    Dim qdf As QueryDef
    Dim sSql As String

    If IsNull(Me.cboTop) Then
        sSql = "SELECT * FROM tData WHERE [Due Date]= #" & forms!myForm!txtDate & "#;"
    Else
        sSql = "SELECT TOP " & Me.cboTop & " tData.* FROM tData WHERE [Due Date] = forms!myForm!txtDate"
    End If

    'save the sql as a qry or open the sql

    Set qdf = CurrentDb.querdefs("qsResults")
    qdf.SQL = sSql
    qdf.Close
    DoCmd.OpenQuery qdf.Name
End Sub

You must concatenate the parts in RED in the code above.
And you must use delimiters around the dates.
Add a Debug.Print to see if the SQL is formed correctly.
Set a breakpoint after the Debug statement, copy the SQL from the immediate window and paste it into a new query. Execute the query to see if records are returned.
To set a breakpoint, click in the line, then press the F9 key.
(For debugging info, see http://www.cpearson.com/excel/debug.htm)

If/when everything is OK, comment out the Debug statement.

Something like this:

Sub btnGo_click()
    Dim qdf As QueryDef
    Dim sSql As String

    If IsNull(Me.cboTop) Then
        sSql = "SELECT * FROM tData WHERE [Due Date]= #" & Forms!myForm!txtDate & "#;"
    Else
        sSql = "SELECT TOP " & Me.cboTop & " tData.* FROM tData WHERE [Due Date] = #" & Forms!myForm!txtDate & "#;"
    End If
    Debug.Print sSql

    'save the sql as a qry or open the sql
    Set qdf = CurrentDb.querdefs("qsResults")  '<<-- set a breakpoint at this line then look at the immediate window for the SQL
    qdf.SQL = sSql
    qdf.Close
    DoCmd.OpenQuery qdf.Name
End Sub

[epardo87]

Date math is done using
DateAdd("d",6,[datefield])

I tried this, but a message appears saying: "this expression is typed incorrectly, or is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

Between Now() And DateAdd("ww",[forms]![frmExpiring]![lstWeeks],Now())

I tested the between formula in a more simple way and it works the way it is below, I just need to change the 20 for the listbox value, I tried it with "[forms]![frmExpiring]![lstWeeks]" but there's no success...

Between Now() And DateAdd("ww",20,Now())