Query Date Range for a Report?? 1

[BluePoet]

Hello,

We have an access database and we have a report that we created. We were wondering if there is a way in access to create a query for the report so that we can input a date range each time we go to use the report? That way we can print reports out for a certain time period instead of for the whole database. Any help would be greatly appreciated.

Thanks

 

[jazzz]

You bet you can what I would do is create a form with two unbound fields where the user would add a start and end date. Once you have those two dates open your report based on a Where Clause that includes the Start and End date.



Life's a journey enjoy the ride...

jazzz

 

[BluePoet]

Thank you Jazzz,

Not sure how to do the later with the opening of the report with the Where Clause but it gives me a good directon to go into. I just created a simple form with two box's not assigned to anything with the start and end date fields. Im presuming I can create a command button in the form to query the report, will just have to do the Where Clause and we'll be set. Thanks for the advice.

 

[jazzz]

You got it. If you need help with the OpenReport on the command button let me know.

Life's a journey enjoy the ride...

jazzz

 

[BluePoet]

Any help you can offer would be great - i created the button can't find out where to put in the option to have it relate to the two boxes for the date.

 

[BluePoet]

Okay - I think I found out where to put the clause at but not sure exactly what to put in to make it work - I clicked on the print command I built and chose build event - which brought me into Microsoft Visual Basic and this is the command it shows:

Option Compare Database

Private Sub Query_Report_Click()
On Error GoTo Err_Query_Report_Click

Dim stDocName As String

stDocName = "Almega CSR Tracking By Salesman New Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Query_Report_Click:
Exit Sub

Err_Query_Report_Click:
MsgBox Err.Description
Resume Exit_Query_Report_Click

End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

I then do a help search for Where Clause and it gives several options but no template to follow to make life easier. So I guess I will need your help some more. Sorry im a newb at access and still trying to figure out this silly thing.

 

[jazzz]

OK BluePoet try this.

Dim varStartDate As Variant
Dim varEndDate As Variant

varStartDate = Me("txtStartDate")
varEndDate = Me("txtEndDate")


DoCmd.OpenReport _
ReportName:="Your ReportName", _
view:=acViewPreview, _
WHERECondition:="YourFieldNameHere BETWEEN #" & _
varStartDate & "# AND #" & _
varEndDate & "#" & ""

I named the fields as txtStartDate and txtEndDate on my form. I capture them in a variant to use in my where clause. I am assuming that the field in your report that stores a date is just that a date field. ReportName should be the name of the report you want to open, YourFieldNameHere will be the name in your table/query that stores the date in the report.

I suggest the book "Access Cookbook" that will show you how to add a control to the unbound fields that will pop up a calendar making it even easier to select date ranges.

Life's a journey enjoy the ride...

jazzz

 

[BluePoet]

Thanks again Jazzz,

I put the code you gave me above - changed the variables you said to change. I get the the date to work correctly now but its giving me a syntax error when I go to use the button. I am presuming its because of the Field Name maybe? The name of the table is CSR Form 2 - the table is called Call Date but I get a, Syntax Error (missing operator)in query expression'(Call Date between #3/1/2006# and #3/10/2006#)'. Any thoughts or suggestions?

 

[jazzz]

You don't want to put the name of the table in the where clause but the name of the date field that I assume is in the table Call Date. You should NEVER use Date as a field name either. What is the name of you date field in the table call date. In the future try not to use spaces in your field names or table names too.

Life's a journey enjoy the ride...

jazzz

 

[BluePoet]

Found the issue - I removed the space between Call Date and just named it CallDate and it worked. Your a rock star or genius or whatever you would like to be called. I will have to find that Access Cookbook to see what it has to offer. Thanks for all your help. I'll take the spacing into advice thats good to know. Thanks again.

Blue

 

[jazzz]

You're more than welcome. Always good to "Pay it forward" when I can.

Life's a journey enjoy the ride...

jazzz

 

[PHV]

If you insist on keeping name with embedded space:
"[!][[/!]Call Date[!]][/!] Between ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886