I need to run a query or report ranging from one date to another (ie: 5/25/2011 - 6/25/2011). I don't understand how to do the "start date" "end date" for the query if I only have one date field in my original table.
I need to run a query or report ranging from one date to another (ie: 5/25/2011 - 6/25/2011). I don't understand how to do the "start date" "end date" for the query if I only have one date field in my original table.
In the query you have to specify the 2 dates of the range by using the BETWEEN...AND.... clause in the query's criteria section
Since SQL view is easier to show here, the query would look something like this
SELECT field1, field2
FROM tablename
WHERE yourdatefield BETWEEN [startdate] AND [enddate]
You can also use an unbound form to supply the dates of the range; that WHERE clause would look like this
WHERE yourdatefiel BETWEEN forms!formname!controlnameforstartdate AND forms!formname!controlnameforenddate
And my suggestion is to not put the criteria in the query and just use the opening of the report to do it (as then you can have a more generic report which doesn't necessarily have to supply a date if you find you don't want to at some point in time but want to use other criteria instead. You can also supplement the criteria at any time to have even another report.
So, the open command would be:
DoCmd.OpenReport "YourReportNameHere", acViewPreview, , "[yourDateFieldNameHere] Between " & Format(Forms!FormName.StartDateControlName, "\#mm\/dd\/yyyy\#") & " And " & Format(Forms!FormName.EndDateControlName, "\#mm\/dd\/yyyy\#")
But jzwp11's way will definitely work as well. I just find that it can be good to have a single generic report and then pass the where clause to it when opening to give you more flexibility.