Access Report - Show the end date of the week
I have an Access 2000 report based on a query like
SELECT
...
FROM Clients AS wc INNER JOIN ...
WHERE ((wo.OfferStatusID)=3) AND
((DatePart("ww",[wo.StatusTimeStamp]))=DatePart("ww",[Enter Week End Date]))
AND ((Year([wo.StatusTimeStamp]))=Year(Date())));
The where clause allows you to enter the "End date of the week" and finds all records for Sunday and Saturday that contain an end date.
My problem: I need to include the Saturday end date in the report so that the title reads "... for the week ending 5/9/09" and I can't figure out how to get that date without asking me asking about it a second time.
Is there a way to get Access to return a parameter that was entered as another field in the results, or another way to get the end date of the week?
a source to share
Another approach is to use a form to capture the request parameters in the first place and reference the form control in the request. For example, in your example, create a form named frmGetDateParam and add a text box named txtDate. Change the format to date, maybe add some checking, doesn't matter. Add a command button to the form that opens the report. Then change your query so that it looks like this: -
SELECT
...
FROM Clients AS wc INNER JOIN ...
WHERE ((wo.OfferStatusID)=3) AND
((DatePart("ww",[wo.StatusTimeStamp]))=DatePart("ww",Forms!frmGetDateParam!txtDate))
AND ((Year([Forms!frmGetDateParam!txtDate]))=Year(Date())));
You can also specify Forms! frmGetDateParam! txtDate as a field in your report.
The only drawback to this approach is that if you try to open a query / report without an open parameter form, the query will still ask for a date value.
a source to share