Open report between two dates based on two dates in text boxes on a fr

[Oliver2003]

Hi, I am trying to Open a report between two dates based on two dates teh user enters into two text boxes on a form with a command button. The code I have is below:

Private Sub Command0_Click()
Dim StartDate As Date
Dim FinishDate As Date

StartDate = Me!StartDate
FinishDate = Me!FinishDate

DoCmd.OpenReport "All DTF's Report", acViewPreview, , "[Date of issue] BETWEEN StartDate AND FinishDate"

End Sub

This sort of works but you have to enter the dates in pop up Enter Parameter Value Boxes of StartDate and FinishDate. I would like these to be obtained from the 2 text boxes on the form.

Any ideas?

 

[Rick39]

Try:

"[Date of issue] BETWEEN #" & Forms!YourForm!StartDate & "# AND #" & Forms!YourForm!FinishDate & "#"

 

[Oliver2003]

This opens the report with no Enter Parameter Value Boxes, but it opens the report for every record?

Any ideas

 

[Rick39]

Can you post the query sql?

 

[Oliver2003]

I'm not sure what you mean? The only code I am using to open this report is in my first post. If you remove StartDate and FinishDate and replace with dates #01/04/03# and #20/04/03# it works great!

Then I tried replacing what you sujested above and it opens the report showing all the records

The report is based on a table

 

[Rick39]

Try

"[Date of issue] BETWEEN Forms!YourForm!StartDate AND Forms!YourForm!FinishDate"

 

[Trendsetter]

This is easiest done in the query for "All DTF's Report"
as criteria against the date field enter:
Between [Forms]![frmName]![StartDate] And [Forms]![frmName]![FinishDate]

frmName would be the name of the form with the command button

 

[Rick39]

Sounds like there is no query (report based on table), but I would agree

 

[Trendsetter]

Hi Rick39,
It would be easy to build a query from the table and would overcome a lot of potential problems.

I think the difficulty with your suggestion of:
"[Date of issue] BETWEEN Forms!YourForm!StartDate AND Forms!YourForm!FinishDate"
is that the " .... " turns the whole thing into a string literal which will not be meaningful to the Date field.

 

[Rick39]

Agreed....that's why I asked for the query earlier - it would make it so much easier

 

[Oliver2003]

The only problem with entering the sql expression into a query that supplies the data to the report is that I would also like to open the report and view all the records sometimes.

The following line works great but to change to dates you have to view the code

DoCmd.OpenReport "All DTF's Report", acViewPreview, , "[Date of issue] BETWEEN #4/4/2003# AND #4/28/2003#"

Then I can just use: DoCmd.OpenReport "All DTF's Report", acViewPreview to view all the records

Getting the date value from the text box does not seem to work!

Any further help would be appreciated

 

[Trendsetter]

If you use the tqo textboxes and put the values into the query NOT the SQL string then you can set the dafault values of the 2 textboxes to be 01/01/2000 and 01/01/2005
which will show all of the records you have - if you want differents dates then manually enter them.
Don't use the SQL version it is flawed!!!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[jaanisf]

DoCmd.OpenReport &quot;All DTF's Report&quot;, acViewPreview, , &quot;[Date of issue] >= StartDate AND [Date of issue] <= FinishDate&quot;