Good morning from the UK!
I have been trying to build a query for a little while using the DateAdd("d",-1,Date() function, but I'm starting to think this is not the right function for my task.
Essentially, I'm trying to return values from a set period, i.e. if today's date is the 12/02/20 (it is at time of writing), I would like to return the previous days data, however I require the function to ignore weekends and other non trading days. Therefore, in the example today's date was the 10/02/20, the query would return values from the 07/02/20 rather than look for values from the 09/02/20.
Now obviously, I could change the code to say -3, but I would like this to be 'once written it's done' function. Furthermore, I would like to have further queries, that return data for the last 10 days, 20 days etc.
I have tried creating an additional table that would only contain the specific dates, and linked that to my main "Sales Data" table, but to no avail.
I'd be grateful to anyone that can help. Screenshot attached.
I have been trying to build a query for a little while using the DateAdd("d",-1,Date() function, but I'm starting to think this is not the right function for my task.
Essentially, I'm trying to return values from a set period, i.e. if today's date is the 12/02/20 (it is at time of writing), I would like to return the previous days data, however I require the function to ignore weekends and other non trading days. Therefore, in the example today's date was the 10/02/20, the query would return values from the 07/02/20 rather than look for values from the 09/02/20.
Now obviously, I could change the code to say -3, but I would like this to be 'once written it's done' function. Furthermore, I would like to have further queries, that return data for the last 10 days, 20 days etc.
I have tried creating an additional table that would only contain the specific dates, and linked that to my main "Sales Data" table, but to no avail.
I'd be grateful to anyone that can help. Screenshot attached.