filter query for certain amount of records from form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kishan - 26 Apr 2005 22:32 GMT

hi, i need some help on queries and sorting data. i have a query based on
data from two separate tables. the query is called "qryEvents". the
information in the query is basically information about events that are
coming up. i need to create an sql statement or something along this line so
that when the user opens the report (based on the query) they are shown the
next 5 events after todays' date.

one way i could do this is by using the "top values" property in the query
properties. however i plan on having a form that will open when the report
opens. i want to have option boxes on this form so that the user can select
how many records they want to see. an easy way would be to ask can u access
the query properties though the form. any ideas would be most welcome. thanks

Reply to this Message

kishan - 28 Apr 2005 12:25 GMT

hmm anyone have any ideas please?

> hi, i need some help on queries and sorting data. i have a query based on
> data from two separate tables. the query is called "qryEvents". the
[quoted text clipped - 8 lines]
> how many records they want to see. an easy way would be to ask can u access
> the query properties though the form. any ideas would be most welcome. thanks

Reply to this Message

Marshall Barton - 29 Apr 2005 19:23 GMT

>hi, i need some help on queries and sorting data. i have a query based on
>data from two separate tables. the query is called "qryEvents". the
[quoted text clipped - 8 lines]
>how many records they want to see. an easy way would be to ask can u access
>the query properties though the form. any ideas would be most welcome. thanks

You can not use a query parameter to specify the TOP value.
Even if you specify it in a "properties" sheet when in the
query's design grid, it is not really a property. All that
glop in the query desgn grid is a convenient tool that
***assists*** you in creating the query's SQL statement,
which is the real thing that is executed.

To do what you want, you have to write some VBA code in the
report's Open event procedure to construct the SQL statement
and stuff that into the report's RecordSource property.

If you need help doing that, then we'll need more details
about your form, where it's called from and how you are
getting the infomation from the form to the report. Just in
case your next question is "what's the best way to do
that?", the form should be opened before the report is
started. The user would enter the query information and,
when done with that, click on a button to open the report.
The code to filter the report's dataset would be in the
button's Click event. The construction of the SQL statement
can also be there, but the report's RecordSource property
must be in the report's Open event.

Signature

Marsh
MVP [MS Access]

Reply to this Message