Create Report by user between 2 dates

[kevin19]

I managed to create a report by user using a combo box. When an user selects a name from the combo box, it generates a report showing all the records by that name.
However, now I need to be able to generate a report as above but between 2 dates. How??
I am having two sets of criteria.
1) by user
2) between 2 dates

Can this be done?

Please help, anyone???
Thanks!

 

[CJ_London]

You must have an appropriate date field in your report query for this to work - even if it is not shown on the report

set the criteria to be WHERE QDate between Form!YourForm!FDate1 and Form!YourForm!FDate2

QDate is the date in your report

FDate1 & 2 are the dates on yourform. The form needs to be open when you run the report

You can use the builder in your report query to access these instead. (right click on the criteria line and select Build)

Note that Between includes the actual dates so if Fdate1 and FDate2 are both 01/01/2012 you will produce a report for 1 day (01/01/2012).

 

[kevin19]

Thanks CJ!
I actually have this criteria in my query under the date field. This works on its own but when I have another criteria under the username field to select the chosen user for the report, the query does not work. Does that mean that I cannot put two criterias in the query? Please advise!

 

[CJ_London]

It sounds like you are using either one criteria or the other

Can you post your query?. You can have multiple criteria in the same report but I need to see what you have to advise further.

 

[boblarson]

Here's a sample of how you can have a reusable report (no criteria in the query underlying the report) and then pass the criteria when you open the report. It makes it quite more flexible.

http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[kevin19]

Here's the query as per your request CJ!
Thanks for helping out!

View attachment Db1.accdb

 

[CJ_London]

Hi Kevin,

As far as I can see, all you need to do is add the fields StartRange and EndRange to your form. The query should work fine.

I would also put in some code to ensure the various print buttons are not enabled until the the fields have all been populated

Alternatively, remove the criteria from the form and enter it into the macro WHERE condition in the macro. Not sure how it is phrased - I don't use macros! You might be better of changing to VBA (there is a macro converter you can use) in which case you would use:

docmd.openreport, "ByAssigneesRpt", acviewpreview,,"CreateDate BETWEEN " & StartRange & " AND " & EndRange & " AND Assignis.Value = " & cboSelectAssignee

With regards your report, the tblTasks.Assignees.Value field is not in the recordset - think it just needs to be Assignees.Value

Also you have a one to many relationship between tbltasks and tblnotes and a right join between tblnotes and tbktasks. The one to many relationship ensures that tblnotes and tbltasks are bound so you dont need the right join - if anything it should be a left join. What the right join is saying is 'list all notes regardless of whether there is an associated task"

Let me know how you get on - I'm away for the next couple of days so may be late in responding

 

[kevin19]

Thanks CJ and Bob!
managed to et it working finally by using the same query based on the 2 textboxes from the form. You guys are great!