Q1 how do I write a query, that no matter what day it’s run it will always filter on a date field with yesterday’s date, or the last seven days?
Q2 Is there a simple way of implementing user input in a query?
In other words could I create a view, which my less tech-savvy colleagues could run in microsoft excel given an account number? and the query would filter by that account number.
A lot of this can be done using the getdate, dateadd, and datepart functions. For instance:
dateadd(d, -1, getdate()) will return the date and time exactly one day ago from this moment.
where datediff(d, fldDate, getdate()) = 1 will filter for all records where fldDate is yesterday… ie from midnight yesterday, up to one instant before midnight.
where datediff(d, fldDate, getdate()) between 0 and 7 will filter for all records where the date is between one week ago today and today, inclusively.
Hope that these help.