Parameters in Reports / Queries

[Chalco]

Hi,

I have a query, which when run asks for the start_date and end_date, it then uses these dates to establish what bookings exist between those two dates (and if a booking straddles either side (or both), it works out what part is between the two dates)

When it runs, it prompts for the start_date and end_date and then retrieves the data - alls well, however, how do I call this passing in the parameters from a report (or even another query for that manner)?

I have tried select * from query_name where start_date=x and end_date=y, but I still get prompted for the values

My aim is to produce a report on bookings which reside in the month

What am I missing ?

Thanks in advance

Richard

 

[CosmoKramer]

Create a report with this query as its Record Source. If you need help, click the New button on the Reports tab and use the Report Wizard. It will guide you through the process....



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Chalco]

I have, But when the report runs, it asks for the two dates - I want to be able to pass these from the form at the time of opening the report and NOT see the parameter requests

 

[CosmoKramer]

Ok, I guess that wasn't clear....

Change the query criteria from the parameters you have now to something like this, of course changing to your object names:

Between [Forms]![frmYourForm]![BegDate] And [Forms]![frmYourForm]![EndDate]

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Chalco]

Sorry Cosmo - that's what I tried in the first place, but... I will try again though


I assume you are meaning "DoCmd.OpenReport reportname, , , "startdate=#01/07/2003# and enddate=#31/07/2003#"

It is the same if I do a new query selecting from the original and specify the fields (they are not part of the retrieved dataset, nor are they on the underlying table)

 

[CosmoKramer]

Do you have text boxes on your form that the user will input the begin and ending dates??



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[Chalco]

Sort of, they select the month and year, I convert that to the first day and last day of the month (but I could hold this in a hidden field if that will cure it !!)

 

[Chalco]

Thanks for you help - I have now sussed it - I qualified the startdate and enddate in the query it'self, rather than trying to pass them as parameters

Not neat, but hey it works !!