Only display records in subform that have a certain date.

[zipaway]

Hello I have a sub form that is connected to a query that displays week dates and the hours for that week.
I was wondering if there is a way to only display the last week, the current week and all future weeks.

So for example, the attached picture should only show the last four entries (01/06/2014 to 22/06/2014).





Any help would greatly be appreciated.

[ItsMe]

Have you tried using the Form's .Filter property?

[ranman256]

On your form, have a box, txtThisWeek, that defaults to the current week.
your query would have a where clause that looks to it (minus 7 days): where [week ending]>= dateAdd("d",-7, forms!frmMain!txtThisWeek)

[June7]

Would have to calculate the last day of the current week then subtract 14 days to establish filter parameter. Base the subform RecordSource on a query with that filter.

You show dates in non-Access standard so you should review: http://allenbrowne.com/ser-36.html

Try:

[Week Ending]>(Date()-(Weekday(Date())-1)+6)-14

[zipaway]

On your form, have a box, txtThisWeek, that defaults to the current week.
your query would have a where clause that looks to it (minus 7 days): where [week ending]>= dateAdd("d",-7, forms!frmMain!txtThisWeek)

I know how to default to the current day, but how in the world do you default to the current week?

And I made a mistake the form is not connected to a query but to a table so I can't put a where clause, unless of course I create a query based on that table then reconnect it to sub form.

[June7]

Calculate the current week ending date. Suggestion in post 4. Use a query or subform Filter property.

[zipaway]

Would have to calculate the last day of the current week then subtract 14 days to establish filter parameter. Base the subform RecordSource on a query with that filter.

You show dates in non-Access standard so you should review: http://allenbrowne.com/ser-36.html

Try:

[Week Ending]>(Date()-(Weekday(Date())-1)+6)-14

Thank you so much for your help, this worked great.