Filter by previous week

Hi all,

I am making a weekly stats graph that i need to put into an existing workbook that makes daily and monthly stats graph. I need to create a function (or somthing) in VBA that gives me 1 week. I will need to use in in an SQL query made in VBA.

I need this to automatically change every time the macro is run and it needs to be for the previous week. it is ran every day to produce daily graphs and it will determine if the daily and monthly have been run for that day or month, I need to do the same for the weekly graph.

I was thinking something like Now() - 7 or TODAY()-7 but I am having no luck

This is what is used to determine the month

EndDate = DateSerial(Year(Date), Month(Date), 0)
StartDate = DateSerial(Year(EndDate), Month(EndDate), 1)

Is there something like this that i can use?

The query for month looks like this:

QueryArray(0) = "SELECT "
QueryArray(1) = " ""call"".""Assigned-to (Team)"", ""call"".""Call Type"", COUNT(*) "
QueryArray(2) = "FROM ""call"" ""call"" "

QueryArray(3) = "WHERE ""call"".""Open-date ~"" > {ts '" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") & "'} "
QueryArray(4) = "AND ""call"".""Open-date ~"" < {ts '" & Format(EndDate, "yyyy-mm-dd hh:mm:ss") & "'} "
QueryArray(5) = "AND (""call"".""Assigned-to (Team)"" = 'pmoc' )"
QueryArray(6) = "group by ""Assigned-to (Team)"", ""Call Type"""


I hope you understand

Can anyone help?

Thanks,

select [call].[Assigned-to (Team)], [call].[Call Type], Count(*)
from [call] where [call].[Open_date] > date()-7 and [call].[Assigned-to (Team)] = 'pmoc'
group by [call].[Assigned-to (Team)], [call].[Call Type]


This should work as sql.

Hi BrianMH,

Firstly, thank you for the reply

I have solved the problem in a similar way the month was determined

Example:

Last_week = DateAdd("ww", -1, Date - Weekday(Date) + 1)
Last_week2 = DateAdd("ww", -1, Date - Weekday(Date) + 7)

queryarray(0) = "SELECT "
queryarray(1) = "COUNT(*) "
queryarray(2) = "FROM ""Call"" ""Call"" "

queryarray(3) = "WHERE ""Call"".""Open-date ~"" >= {ts '" & Format(Last_week, "yyyy-mm-dd hh:mm:ss") & "'} "
queryarray(4) = "AND ""Call"".""Open-date ~"" <= {ts '" & Format(Last_week2, "yyyy-mm-dd hh:mm:ss") & "'} "
queryarray(5) = "AND (""Call"".""Assigned-to (Team)"" = 'pmoc' )"

Anyway, Thank you for your reply

Thanks :)