Access Report - Show Week Ending Date

Question

I have an Access 2000 report based on a query like this

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 'Week End Date' and it finds all of the records for the Sunday-Saturday week that contains the end date.

My problem is I need to include the Saturday end date on the report so that the header reads '... for week ending 5/9/09' and I can't figure out how to get that date without the query asking me for it a second time.

Is there a way to force Access to return the parameter entered as another field in the results, or another way to get that week ending date?

Answer 1

Continuing to poke around in the query designer I discovered that I could add this to the SELECT clause and get the value entered added to each row:

[Enter Week End Date] AS WeekEndDate

This works, but I am still open to other suggestions.

Answer 2

You could stack two queries (make one the source of the other). This would be pretty MS Access'y. However, if you have it working now, I'd stick with what you have. It's probably cleaner.

Answer 3

Another approach is to use a form to grab the query params first, and reference the form control in the query. For instance, with your example, create a form called frmGetDateParam, and add a textbox called txtDate. Change the format to a date, perhaps add some validation, doesn't matter. Add a command button to the form which opens up the report. Then, change your query to look 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 reference Forms!frmGetDateParam!txtDate as a field on your report.

The only downside to this approach is that if you try to open the query/report without the parameter form being open, then the query will still prompt you for the date value.