Command Button to view previous week's records

[Ben]

I have a subform that has two fields: ActionItems and Owner.
I want to have a button that when you click it it shows all the ActionItems
and Owner from the previous week.

On the Main form there is a start date and end date. For this week it was
Aug 11-Aug 18.
On the SubForm, you can see the previous week's ActionItems and Owners only
if you click the record selector button. I want to get a button to popup a
form or datasheet that shows the previous week's records.

Ben

 

[Michael Gramelspacher]

I have a subform that has two fields: ActionItems and Owner.
I want to have a button that when you click it it shows all the ActionItems
and Owner from the previous week.

On the Main form there is a start date and end date. For this week it was
Aug 11-Aug 18.
On the SubForm, you can see the previous week's ActionItems and Owners only
if you click the record selector button. I want to get a button to popup a
form or datasheet that shows the previous week's records.

Ben

I think this gets last week's records:

SELECT * FROM SomeTable WHERE (((some_date) Between
Date()-DatePart("w",Date()-1)-7 And Date()-DatePart("w",Date()-1)-1);

I would probably reset the subform recordsource in the command button's Click event.

Another more versatile way is to have a text box to input a date and a combobox next to it to select
items like this week,next week, last week, two weeks ago, this month, last month, etc.

In the combobox's AfterUpdate Event set up a Select Case and a Case for each of the items. If the
text box is blank, then the from date in the calculations would be Date(), i.e., today. Otherwise
the from date is the date entered in the text box.

Of course, if all you really will ever need is last week, then go with a command button.