How can input in the Access Report that it display report
base on two input (dates) ?

I want to give two input in textbox (assign start date) and assign end date) it display records between these dates ?

first input assign start date 03/01/2004 (m/d/y)
second input assign end date 03/31/2004 (m/d/y)

assign start date field is astrdate
assign end date field is aenddate
in the table


after click command button it display records (retrieve all records which are between assgin start date and assign end date).

how can use following query in Access Report ?


SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno
where assignments.aenddate between '03/01/2004' and '03/31/2004'


Please help, guide the steps how can input the two date
in the Access Report it display report base on these two
input dates ?

Regards.

Mateen

Well, you can do one of two things. Try substituting the single quotes (') in your query to pound signs (#). The single quote deliminates text, the pound sign deliminates dates.

where assignments.aenddate between #03/01/2004# and #03/31/2004#

Or you can put this on your form's command button that opens the report.

DoCmd.OpenReport "Your Report", acViewPreview, , _
"[aenddate] >= #" & Me.astrdate & "# And [aenddate] <= #" & _
Me.aenddate & "#"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Curiously the text delimited dates seem to work in many queries. Date/Time fields are stored as doubles and using text delimiters imposes an implicit type conversion to string. This may result in the loss of use of any indexing on the date field resulting in less than optimal performance. For this reason, it is wise to follow Greg's suggestion regarding the delimiters.

An alternative to using a filter in the OpenReport paramters as suggested is that you may also set the SQL property of a saved querydef used as the report recordsource. The downside of this approach is that query compilation will be lost and the changing of saved objects results in a small amount of file bloat (recovered on compacting). The upside of loss of query compilation is that DAO queries frequently run faster where the query is changed to restrictive where conditions because the compiled plan is no longer appropriate. If performance becomes an issue as the database grows, you may wish to investigate further.

If you have subreports not directly contingent on linking fields, setting the SQL property of a saved querydef for the subreports may be the only solution.

You may wish to consider the use of paramaterized queries. This is an area I plan to investigate in greater detail in the near future.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

thank you very much for your help.

first option is working.
but in the second option, I try

Dim stDocName As String

    stDocName = "Supervsion Staff Assignment(input4)"
    DoCmd.OpenReport stDocName, acPreview, , _
    "[aenddate] >= #" & Me.astrdate & "# And [aenddate] <= #" & _
    Me.aenddate & "#"

it give error message that
"compile error, Method or date member not found"

you like to explain what is the method or date member in the error?

main problem is solve.

Mateen

In means either one of two things. Either the variable AENDDATE doesn't exist in your report "Supervsion Staff Assignment(input4)", or one/both of the textboxes (Me.astrdate/Me.aenddate) doesn't/don't exist on your form.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

thanks.