Show all dates in any given month / quarter

[TessB]

I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?

The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.

Thanks!
Tess

 

[antomack]

Not sure how to build something to give all dates for a quarter but the following should allow you to get all dates for a particular month.

1) Set up a table with 1 number field, DayNo
2) Add records for numbers 1 to 31
3) Build a select query as follows
SELECT [DayNo] & "/" & [Month No?] & "/" & [Year No?] AS MonthDates
FROM YourTable;
4) Build another select query on the query from query in part 3 as follows
SELECT CDate([MonthDates]) AS DaysInMonth
FROM YourQuery
WHERE (((IsDate([MonthDates]))=True));

Running the query from part 4 will prompt you for a 'Month No?' and a 'Year No?' which when entered will give you all proper dates for that month.

 

[antomack]

Creating a second table to hold a reference to which quarter each month belongs to will allow you to edit the queries so as to give all the dates in a quarter or over a number of consecutive quarters or even for a whole year assuming your quarters correspond to the calendar quarters. If the quarters for your year traverse two years then this method will not work or if the months that make up a quarter change from year to year.

So as before
1) Set up a table with 1 number field, DayNo
2) Add records for numbers 1 to 31
3) Set up a second table with 2 number fields for QtrNo and MonthNo
4) Add the twelve records for each of the months with it's corresponding quarter no 1 to 4

If you just want the days in a month then use queries from 3 and 4 in previous posting.

To select dates in a quarter or over more than one quarter use the following SQL
5) Create query as follows
SELECT [DayNo] & "/" & [Month No?] & "/" & [Year No?] AS MonthDates
FROM MonthTable, QtrTable
WHERE [QtrTable.QtrNo] BETWEEN [Start Qtr?] AND [End Qtr?];
6) create second query
SELECT CDate([MonthDates]) AS DaysInMonth
FROM YourQuery
WHERE (((IsDate([MonthDates]))=True))
ORDER BY CDate([MonthDates]);

Running the query from 6 will prompt you for the 'Year No?', 'Start Qtr?' and 'End Qtr?' and will return all valid dates for specified quarters.

 

[TessB]

Excellent!
Thank you very much.