Rather than having a pop up screen for the date range in my queries, I would like to set them up to run for either the last day or last 7 days (depending on which query of course).
I am confused about the way to do this - seems like from other posts it's using "DateAdd" or maybe the "Date, -7" .... but I haven't been able to get this to work.
Here is the SQL from my query and where right now in the WHERE it is having the user enter the date each time they run the query.
SELECT SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CASE.SWTYPE, Team_Type.Team_Type, SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.SWDATECREATED, SWB_SW_CASE.SWDATERESOLVED, SWB_SW_PROD_RELEASE.SWNAME, SWB_SW_CASE.SWCREATEDBY, Employee_Team.TEAM, DateDiff("d",SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED]) AS CRT, SWB_SW_CASE.AISSUETYPE, SWB_SW_CASE!ATIMESPENT/60 AS Hours_Spent, SWB_SW_CASE.ATIMESPENT
FROM ((SWB_SW_PROD_RELEASE INNER JOIN ((SWB_SW_CASE INNER JOIN SWB_SW_CUSTOMER ON SWB_SW_CASE.SWCUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID) INNER JOIN SWB_SW_INST_PRODUCT ON SWB_SW_CASE.SWINSTPRODID = SWB_SW_INST_PRODUCT.SWINSTPRODID) ON SWB_SW_PROD_RELEASE.SWPRODRELEASEID = SWB_SW_INST_PRODUCT.SWPRODRELEASEID) LEFT JOIN Team_Type ON SWB_SW_CASE.SWTYPE = Team_Type.Type) LEFT JOIN Employee_Team ON SWB_SW_CASE.SWCREATEDBY = Employee_Team.SWLOGIN
WHERE (((SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And (SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null) AND ((SWB_SW_CASE.SWTYPE) Is Not Null) AND ((SWB_SW_CASE.SWDATECREATED) Between [From Date/Time:] And [Through Date/Time:]) AND ((SWB_SW_CUSTOMER.ASD)=1));
Any suggestions?
I am confused about the way to do this - seems like from other posts it's using "DateAdd" or maybe the "Date, -7" .... but I haven't been able to get this to work.
Here is the SQL from my query and where right now in the WHERE it is having the user enter the date each time they run the query.
SELECT SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CASE.SWTYPE, Team_Type.Team_Type, SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.SWDATECREATED, SWB_SW_CASE.SWDATERESOLVED, SWB_SW_PROD_RELEASE.SWNAME, SWB_SW_CASE.SWCREATEDBY, Employee_Team.TEAM, DateDiff("d",SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED]) AS CRT, SWB_SW_CASE.AISSUETYPE, SWB_SW_CASE!ATIMESPENT/60 AS Hours_Spent, SWB_SW_CASE.ATIMESPENT
FROM ((SWB_SW_PROD_RELEASE INNER JOIN ((SWB_SW_CASE INNER JOIN SWB_SW_CUSTOMER ON SWB_SW_CASE.SWCUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID) INNER JOIN SWB_SW_INST_PRODUCT ON SWB_SW_CASE.SWINSTPRODID = SWB_SW_INST_PRODUCT.SWINSTPRODID) ON SWB_SW_PROD_RELEASE.SWPRODRELEASEID = SWB_SW_INST_PRODUCT.SWPRODRELEASEID) LEFT JOIN Team_Type ON SWB_SW_CASE.SWTYPE = Team_Type.Type) LEFT JOIN Employee_Team ON SWB_SW_CASE.SWCREATEDBY = Employee_Team.SWLOGIN
WHERE (((SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And (SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null) AND ((SWB_SW_CASE.SWTYPE) Is Not Null) AND ((SWB_SW_CASE.SWDATECREATED) Between [From Date/Time:] And [Through Date/Time:]) AND ((SWB_SW_CUSTOMER.ASD)=1));
Any suggestions?