display records based on a prompted criteria

[vasu1211]

My report has a field called as shipments besides other fields . There are hundreds of records. Now i want to see only those records where no of shipments is greater than say x value. I want the report to prompt the user to enter the minimium no of shipments and then display the records which meet the criteria.

How to do this in a report. Please explain step by step as i am new to this.

 

[dhookom]

IMHO, you should never use parameter prompts in queries or record sources. Use controls on forms.

Does your report's record source have a field that stores the number of shipments?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[vasu1211]

my report record source is a query- which stores the no of shipments.

 

[dhookom]

Add a text box to a form that allows the user to enter the minimum number of shipments. Then use code in that form to open the report based on the value of the text box. You can use the command button wizard to write most of your code and then modify the code like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtMinNumShip) Then
   strWhere = strWhere & " And [no of shipments]>=" & _
      Me.txtMinNumShip
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mainphramephrea]

Somewhat of a piggyback to the last one here. I have a report that I would like to filter based on three different criteria. Is there a way to create that same type of form to filter by two or even three different controls? Thanks!!

 

[dhookom]

If the fields to filter on are in the report's field list, you can use code like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtMinNumShip) Then
   strWhere = strWhere & " And [no of shipments]>=" & _
      Me.txtMinNumShip
End If
If Not IsNull(Me.txtStartDate) Then
   strWhere = strWhere & " And [ShipDate]<=#" & _
      Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
   strWhere = strWhere & " And [ShipDate]>=#" & _
      Me.txtEndDate & "# "
End If

DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]