parameters

  • Thread starter Thread starter Susie
  • Start date Start date
S

Susie

This is going to be a difficult question to ask. I have a
report that requires choosing a category which I have used
a form with a dropdown list for the user to choose. I
have used the traditional form attached to the query and
report to filter the required criteria. Now I have
another field that requires them to choose two dates and
the criteria falls between those two dates. Right now I
just have it set up as a parameter in the query and the
user is prompted. However I think the pop-up form is
interfering with the process because the user will choose
from the dropdown, then the dates are entered into the
parameter boxes that appear. But after the dates are
entered, another parameter box appears asking for the same
information picked from the dropdown in the form. (It
seems the "visible" field in the macro is getting
confused). Is there a way I can add the dates (a start
date and an end date) on the form? If so, how do I enter
this into the query (ex [forms]![choosedate]![start date]??
[enddate]??(between??). I'm confused because the "date"
field is just one field and I am asking for two dates. I
tried using the "between" in the criteria with the example
above, but I'm not doing something right? Please help if
you can (understand). Thank you in advance....
 
I am going to recommend a slightly different approach.

I will require that you do use a "bit" of code, but I think
this approach will work better. Here is what I suggest:

First, remove all parameters from the query. The result
will be nice clean sql. No funny forms expressions,
and even better is the fact that now the query is NOT attached to a
particular form that may, or may not be opened. Also, sql is SOOOO much
nicer when you don't have a bunch of ugly forms stuff in sql (that is not
much standard anyway).

The above makes it also easy to design, and test, and setup your report..as
you now don't have to enter a bunch of values.

Ok...we got nice clean sql. Now, lets do the conditions stuff we want. The
trick involves using the "where" clause of the open report. This clause is
simply a where sql clause without the word "where". This feature will allow
us to send any condition to the report, but NOT need sql with parameters.

So, on your nice cute report prompt form, have a start date, end date text
box, and of course your droplist for the category.

Our code behind the button will look like: (I assume a form call frmPrompt)

dim strWhere as string

strWhere = "(InvoiceDate between forms!frmPrompt!StartDate and
forms!frmPrompt!EndDdate)"

strWhere = strWhere & " and Catagory = forms!frmPromptcboCatagory"

docmd.OpenReport "yourreport",acViewPreview,,strWhere

The above should do the trick. And, if you want to see some nice screen
shots of the the above code, and how it looks..take a look at the follwing:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
 
Back
Top