In an application (related to the Covid-19 pandemic in the north of Brazil) I've to generate a series of crosstabs queries, a few based on current week's collected data (Sunday being the first day of the week).
To limit the query to the current week is simple with a WHERE DatePart("ww",[RecDate]) = DatePart("ww",Date()) clause.
However, my problem is to find a way to automatically fill the Column Headings (in Query Properties) with the proper (seven) dates (dates, not names), one column for each day of the current week (starting Sunday) and also in the related form where the results are displayed, otherwise I'm forced to update several crosstabs queries and related forms every week.
To place the dates as rows is not an option because some of the tables would have an unknown number of columns and headings.
Any ideas how to accomplish the task?
To limit the query to the current week is simple with a WHERE DatePart("ww",[RecDate]) = DatePart("ww",Date()) clause.
However, my problem is to find a way to automatically fill the Column Headings (in Query Properties) with the proper (seven) dates (dates, not names), one column for each day of the current week (starting Sunday) and also in the related form where the results are displayed, otherwise I'm forced to update several crosstabs queries and related forms every week.
To place the dates as rows is not an option because some of the tables would have an unknown number of columns and headings.
Any ideas how to accomplish the task?