By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Looping through recorset to capture missing dates

Jerry Maiapu
100+
P: 259
I HAVE THIS Sql Query ( qrysummary) :

Expand|Select|Wrap|Line Numbers
  1. SELECT [Summary 2weeks].Salutation, 
  2. [Summary 2weeks].[Full Name],
  3.  [Summary 2weeks].[Days Worked], 
  4. IIf((WorkingDays([Forms]![Parameter Collector]![Oyear],
  5. [Forms]![Parameter Collector]![Ndate]))-([Days Worked])<0,0,
  6. (WorkingDays([Forms]![Parameter Collector]![Oyear],[Forms]![Parameter Collector]![Ndate]))-([Days Worked])) AS [Days Absent], 
  7. IIf(IsNull([Overtime Summary.CountOfDateWorked]),0,[Overtime Summary.CountOfDateWorked]) AS [Overtime Days],
  8. [Summary 2weeks].SumOfHrtMintNoAOT, 
  9. [Summary 2weeks].SumOfHrtMintAOT, 
  10. IIf(IsNull([Overtime Summary].[Sum Of Hours]),0,[Overtime Summary].[Sum Of Hours]) AS [Overtime Hrs],
  11. IIf(IsNull([Overtime Summary].[Sum Of Minutes]),0,[Overtime Summary].[Sum Of Minutes]) AS [Overtime Mins],
  12. Credits_All_Total.SumOfFinalResetedCredit, FirstnLAstEmployess.AbsentCredit, [SumOfFinalResetedCredit]-[AbsentCredit] AS OverallCredit
  13. FROM FirstnLAstEmployess INNER JOIN ([Overtime Summary] RIGHT JOIN (Credits_All_Total INNER JOIN [Summary 2weeks] 
  14. ON Credits_All_Total.EmployeeID = [Summary 2weeks].EmployeeID) ON [Overtime Summary].EmployeeID = Credits_All_Total.EmployeeID) ON FirstnLAstEmployess.Employees_EmployeeID = Credits_All_Total.EmployeeID 
  15. ORDER BY [Summary 2weeks].[Full Name];

There about 4 queries that are tied together in this single query. No need to go through,, it is working fine.

As you can see for some queries, there are criteria collected from a form (Parameter Collector) to filter the results.

Ok, the result of this query presents a bunch of dates with some related data that falls between the date range specified on the form. (Just usual stuff)

I report is generated based on this query.

The problem aroused when my boss said that the report is displaying only dates with data but he said he wants all date inclusive,. Does not matter it has data or not for accurate reporting purposes.

This forced me to post this question.

What I would like to do is to retrieve somehow the dates that are not returned by the query.

To do this I want to use VBA to loop through the Recordset (query above) [qrysummary] and get each date that is not returned and inserted into a new Recordset with the final results.

Hereafter, the report can be based on the new Recordset..which will include the missed dates:


For example if first query displays the following filtered between Dates: 10/07/2010 and 20/07/2010,



Date...........Data1.....Data2

11/07/2010.....bla.......bla

12/07/2010.....bla.......bla

15/07/2010.....bla.......bla

17/07/2010.....bla.......bla


Then
Dates 13,14,16,18,19 of the same month..07/10.. are missing. Thus,
the final SQL/Recordset should iterate through the result above and use the current date calender to return all the dates within the range. It would be something like


Date..........Data1.... Data2

11/07/2010.....bla.......bla

12/07/2010.....bla.......bla

13/07/2010.....nodata....... nodata

15/07/2010.....bla.......bla

16/07/2010.....nodata....... nodata


17/07/2010.....bla.......bla

18/07/2010..... nodata.......nodata

19/07/2010.....nodata....... nodata



Hereafter, I could use an update sql command to probably update a temp query or so, so that my report is updated..

Any idea/suggestion on how about going around this situation is welcomed,
could be the way I suggest or any other possible ways is greatly appreciated.

Thanks a lot in advanced..
Jul 28 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.