Previous Week

[safari7391]

Hello Everyone,

Have a problem, hope someone can help.

MS Access 2002

How to query data from a daily date field [row_date], for any dates that are from the previous week (from Sunday to Saturday). No matter which day the query is ran it will bring back the previous week data.

 

[earthandfire]

Try the following:

Function FirstDayOfLastWeek(d As Date) As Date

FirstDayOfLastWeek = (d - 7) - WeekDay(d) + 1

End Function

Function LastDayOfLastWeek(d As Date) As Date

LastDayOfLastWeek = FirstDayOfLastWeek(d) + 6

End Function


Hope this helps.

 

[melaniecarr23]

My question is along the same lines - how to select records based on the following work week (Monday - Friday).

I could use an iif statement based on the day of the week it is and adding the correct # of days, but is there any function that would return the date of a specific weekday?

Thanks,
Melanie

 

[earthandfire]

Melanie, try this

Function FirstDayOfNextWeek(d As Date) As Date

FirstDayOfNextWeek = (d + 7) - WeekDay(d) + 2

End Function

Function LastDayOfNextWeek(d As Date) As Date

LastDayOfNextWeek = FirstDayOfNextWeek(d) + 4

End Function

Hope this helps.

 

[safari7391]

How would I use the the 2 formulas you provided in the critera section of my date field? I am only trying to get the previous week data.

 

[earthandfire]

Something like this

You could simplify the functions as follows:

Function FirstDayOfLastWeek() As Date

FirstDayOfLastWeek = (Date - 7) - WeekDay(Date) + 1

End Function

Function LastDayOfLastWeek() As Date

LastDayOfLastWeek = FirstDayOfLastWeek() + 6

End Function


WHERE [datefield] Between FirstDayOfLastWeek And LastDayOfLastWeek


Hope this helps.

 

[safari7391]

This is only returning the same field as the date field. I only need to get the previous week. Do you have any other suggestions?

 

[earthandfire]

I'm afraid I don't understand.

If you have an SQL Statement along the lines of:

SELECT CommaSeparatedFieldList FROM TableName WHERE [datefield] Between FirstDayOfLastWeek And LastDayOfLastWeek


That will return all the required fields from your table where the value in the datefield is any day in the previous week.

Hope this helps.

 

[PHV]

In the criteria for [row_date]:
Between (Date()-Weekday(Date())-6) And (Date()-Weekday(Date()))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886