JEEP
04-30-2012, 02:58 AM
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,
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,