Interesting question in a Forum whereby the Poster wanted a list of the Quarters between two dates.\u00a0 While this request doesn’t come up often when it does there’s an answer…<\/p>\n
While you *think* you could run this in one query it’s just not possible because in order for the query to provide the answer it needs data as in dates to process.\u00a0 Other than that you are just going to get the beginning Quarter and the ending Quarter.\u00a0 The query cannot *think* those dates into existence.\u00a0 So, we have to *trick* it into seeing those dates.\u00a0 (You could use this same procedure for projecting months or years, as well.)<\/em><\/p>\n Step 1<\/b> Side note:\u00a0 A way faster way to get those values in a table is inside a transaction with a QueryDef (posted by MarkK in Access World Forums<\/a>)\u00a0 Just copy\\paste the below code in a new Module<\/strong> and then put…<\/p>\n …in the Immediate<\/strong> window and press enter.<\/p>\n Tip<\/strong>!\u00a0 If you download the sample file below tblCount is included and already populated and Module is included for future use.<\/em><\/p>\n Step 2<\/b> and add\u00a0<=[pEndDate]<\/strong>\u00a0to the Criteria<\/strong> section of that one field\u00a0(example of what you should have below)…<\/p>\n Note<\/strong>: When you preview the query provided you have entered a Start Date and and End Date you will see a record for every day in the Project.<\/em><\/p>\n Step 3<\/b> …on the Field<\/strong> line below (example below).\u00a0 You should now see all the\u00a0Quarters<\/strong> between two dates entered.<\/p>\n All done!\u00a0 You can download the sample database here<\/a>.<\/p>\n
\nIn your database create a table named tblCount<\/strong> and add one field named dNumber<\/strong> – Long Integer and make it a Primary Key<\/strong>. Start with the number 0(zero) and continue adding until you get to 1724.\u00a0 (Okay, tedious but worth it so just keep lugging away till you get to that number.)<\/em><\/p>\n\r\n?fInsertNumbers()\r\n<\/pre>\n
\r\nOption Compare Database\r\nOption Explicit\r\nConst SQL As String = "INSERT INTO tblCount(dNumber) VALUES( p0 )"\r\nFunction fInsertNumbers()\r\n'From https:\/\/www.access-programmers.co.uk\/forums\/showthread.php?t=300693\r\n \r\n Dim i As Integer\r\n \r\n DBEngine.BeginTrans\r\n With CurrentDb.CreateQueryDef("", SQL)\r\n For i = 1 To 1724\r\n .Parameters(0) = i\r\n .Execute\r\n Next\r\n .Close\r\n End With\r\n DBEngine.CommitTrans\r\n\r\nEnd Function\r\n\r\n<\/pre>\n
\nCreate a SELECT<\/strong> query (mine is named qryGetProjectDays<\/strong>) based off of the Table that has the Project Start Date and End Date and drop tblCount<\/strong> in and do NOT<\/strong> <\/span>create any Joins. You need one field…<\/p>\n\r\nTheDays: DateAdd("d",[dNumber],[pStartDate])\r\n<\/pre>\n\r\nSELECT DateAdd("d",[dNumber],[pStartDate]) AS TheDays\r\nFROM tblYourTable, tblCount\r\nWHERE (((DateAdd("d",[dNumber],[pStartDate]))<=[pEndDate]))\r\nORDER BY DateAdd("d",[dNumber],[pStartDate]);\r\n<\/pre>\n
\nCreate a new SELECT<\/strong> query and place qryGetProjectDays<\/strong> (or whatever you named your query) in your new SELECT<\/strong> query. Place…<\/p>\n\r\nCDate(Format([TheDays],"q-yyyy"))\r\n<\/pre>\n
\r\nSELECT CDate(Format([TheDays],"q-yyyy")) AS ProjectQuarters\r\nFROM qryGetProjectDays\r\nGROUP BY CDate(Format([TheDays],"q-yyyy"));\r\n<\/pre>\n<\/div>\n