Reference A Query Parameter From A Form

[hmayes]

Hello All,

I'm using Access 2003 on Windows XP. I'm real new to Access and SQL, so bear with me.

I have a report that is bound to a query. The query has a parameter for start and end date. This works fine right now, when I run the report I get a dialog box for each parameter. What I would like to do though is create a form that fills in the parameters. That way I can make buttons in the form like "Current year" "Year to Date" etc, as well as have default values in the fields. I tried making a bound form (as I understand it, an unbound form won't link back to the query), and linking the two text boxes in my form to the corresponding parameters in my query. However, when I open the form, I still get two dialog boxes asking me for dates. Does anyone know how I can accomplish this? Any help would be greatly appreciated. Below is the query I am working with (I know, it's probably real sloppy, but it's the first real-world query I've written):

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT 
d.[c.customerid] AS [Customer ID], 
COUNT(d.[c.customerid]) AS [Number Of Orders], 
d.[c.fullname] AS Name, 
d.[c.company] AS Company, 
d.[c.email] AS Email, 
d.[c.phone] AS Phone, 
d.[c.discountpercent] AS [Percent Discount], 
d.[c.taxid] AS Terms, 
MAX (d.[o.orderdate]) AS [Last Order], 
MIN (d.[o.orderdate]) AS [First Order], 
AVG (d.[o.producttotal]) AS [Average Order]
FROM (SELECT c.customerid, c.fullname, c.company, c.email, c.phone, c.discountpercent, c.taxid, o.orderdate, o.ordernumber, o.producttotal
FROM customers AS c, orders AS o
WHERE c.discountpercent > 0 AND c.customerid = o.customerid AND o.orderdate BETWEEN [StartDate] AND [EndDate]) AS d
GROUP BY d.[c.customerid], d.[c.fullname], d.[c.company], d.[c.email], d.[c.phone], d.[c.discountpercent], d.[c.taxid]; 

Thanks,
Hal

[shamshe]

Remove PARAMETERS

and this portion of WHERE clause:
orderdate BETWEEN [StartDate] AND [EndDate]
should go into your form if they are not already there

[LICKY]

Keep in mind that the easiest way to build a query is to use the QBE (Query By Example) query builder to build your query. If you put a start date and end date field on your form you can use the builder option (3 dots) to reference the active form. It is always a good idea to give distinct name to all items on forms. When selecting a range of data it is always a good idea to use the >= and <= selection criteria, e.g. >= startdate and <= finish date. That way you will always get the full data set.

[KLMCats]

In addition to the other suggestions, what I have done for a number of projects is create a single record table: tblReportParameters that is bound to a form for defining beginng/ending date, fiscalperiod - whatever. I then include the table in queries used for reporting such as DateField between beginningdate and endingdate

This way any report can be run multiple times w/out the form having to be open. Otherwise you would be coding DateField between forms!formname!beginningdate and forms!formname!endingdate - returning an error if you (the programmer) try to open the query or the report w/out the form being open