Start and End Date Count Records Open per Day

Change each instance of "INNER" to "LEFT" and see what that does.
 
Hey that worked out really well - all dates are now listed. Quick question though - on the Open Issues by Date, the end of the month only shows 1 issue open when I know that there were more issues opened on that date - they just haven't been closed yet. Here is the query for Open Issues:

Base:

SELECT [Index], [DateReceived], [Closed], tblReportDate.ReportDay
FROM tblReportDate INNER JOIN tblIssues ON ([tblReportDate].[ReportDay]>=[tblIssues].[DateReceived] AND [tblReportDate].[ReportDay]<=[tblIssues].[Closed])
ORDER BY [Index];

And then Count:

SELECT qryAgeOpenBase.ReportDay, Count(qryAgeOpenBase.ReportDay) AS [Open]
FROM qryAgeOpenBase
GROUP BY qryAgeOpenBase.ReportDay;
 
Should I alter the closed part of the Base Statement? I feel like that is where my error is.
 
Try

SELECT [Index], [DateReceived], [Closed], tblReportDate.ReportDay
FROM tblReportDate INNER JOIN tblIssues ON ([tblReportDate].[ReportDay]>=[tblIssues].[DateReceived] AND [tblReportDate].[ReportDay]<=[tblIssues].[Closed] OR [tblIssues].[Closed] Is Null)
ORDER BY [Index]
 
Alright I just tried that but it gave me far more Open issues than I actually had open on each date. I played with it for a bit and this worked out:

SELECT [Index], [DateReceived], [Closed], tblReportDate.ReportDay
FROM tblReportDate INNER JOIN tblIssues ON ([tblReportDate].[ReportDay]>=[tblIssues].[DateReceived] AND [tblReportDate].[ReportDay]<=[tblIssues].[Closed]) OR( [tblReportDate].[ReportDay]>=[tblIssues].[DateReceived] AND [tblIssues].[Closed] Is Null)
ORDER BY [Index];
 
Thank you again. Also - I am running into a problem as I graph: for Opened, Closed, and Overdue, there are days that have one or more of these not occurring and thus the day shows up blank in the query I use to make the graph. Is there any way I can make null values show up as zero?
 
Okay I just got it. In the query I just make an IIF statement for each. Like this:

Expr1: IIf([Closed] Is Null,"0",[Closed])
 
Alright I got all of this to work, but now on my graph there are way too many dots haha - is there a way to only take every 3rd or 4th? I see that you can change the Axis, but how do you change the data? I am guessing that this will have to be done back in the query.
 
Sorry, I missed the parentheses on the join. This appeared to work in your test data:

SELECT [IssueName], [ArrivalDate], [ClosedDate], [tblDates].[ReportDate]
FROM tblDates INNER JOIN tblIssues ON ([tblDates].[ReportDate]<=[tblIssues].[ClosedDate] Or [ClosedDate] Is Null) AND ([tblDates].[ReportDate]>=[tblIssues].[ArrivalDate])
ORDER BY [IssueName];

I would use the Nz() function to replace the Nulls with zeros, but the IIf() is fine. As to graphs, I really haven't done much with them. You want every 3rd or 4th day? I suspect the way to do that is simply to put the dates you want in the table. I just tested by putting 1 day a week in there, and the query seemed to pull correctly. Presuming testing proves that out, you could modify your recordset/loop to put in every 3rd day instead of every day, or whatever.
 
The date suggestion worked well. Now onto other parts of the database. Thanks a lot Paul!
 
Hey I am having a hard time finding any information on how to fill the date table mentioned earlier. Any suggestions as to how I would fill it? I looked for recordset but I did not find anything.
 
Really? I just searched this site and it returned 20 pages of threads. Here's a snip out of a database:

Code: 
    Dim rsReser As DAO.Recordset

    Set rsReser = CurrentDb.OpenRecordset("tblReservations", dbOpenDynaset, dbSeeChanges)
    
      For intCars = 1 To Me.Num_cars 'add res for each car
        rsReser.AddNew
        rsReser("phoneid") = Me.PhoneID
        rsReser("landmark") = Me.Landmark
        rsReser("zone") = Me.zone
        rsReser.Update
      Next intCars
    
    Set rsReser = Nothing
 
Thanks again. I was just using google. Next time I will search through this site first.
 
Alright, I have the Reports form that has [frmReports]![StartDate] and [frmReports]![EndDate]. I want my table, tblReportDates, to autopopulate all days between these two dates. So after I hit the generate Report button, before the report is generated, I would like to have the table updated.

Here is what I have going:

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim rsReser As DAO.RecordSet
Set rsReser = MyDBName.OpenRecordSet ("tblReportDates","ReportDay")



Dim stDocName As String
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Exit_Command45_Click:
Exit Sub
Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub
 
Didn't mean to hit enter. Let's try this again. (This is my first time attempting to use recordset).

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

Dim rsReser As DAO.RecordSet
Set rsReser = MyDBName.OpenRecordSet ("tblReportDates","ReportDay")

For ReportDay Between [frmReports]![StartDate] And [frmReports]![EndDate]
rsReser.AddNew
rsReser("ReportDay") = Me.ReportDay
Next ReportDay

Set rsReser = Nothing


Dim stDocName As String
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Exit_Command45_Click:
Exit Sub
Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub
 
Okay this obviously does not work. I am working on the code right now. Will post it in a few minutes.
 
Declare 2 date variables. Set one to the start date and one to the end date. Then you use a loop (air code):

Code: 
Do While StartVariable <= EndVariable
  add a record to the table using the start variable
  add a day to the start variable
Loop
 
Okay this is what I have going so far:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim rsReser As DAO.Recordset
Set rsReser = CurrentDb.OpenRecordset("tblRecordDate", dbOpenDynaset, dbSeeChanges)

For intCars = 1 To Me.tblRecordDate 'add res for each car
rsReser.AddNew
rsReser("ReportDay") = [frmReports]![StartDate]

rsReser.Update
Next ReportDay

Set rsReser = Nothing
Dim stDocName As String
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

But how would I get this to populate the ReportDate table with [StartDate], [EndDate] and a handful of dates in between?
 
Oh, and don't forget to empty the table at the start of the process:

CurrentDb.Execute "DELETE * FROM TableName"

your code to open the recordset is also incorrect. Check my example again.
 
Alright:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
CurrentDb.Execute "DELETE * FROM tblRecordDate"
Dim rsReser As DAO.Recordset
Set rsReser = CurrentDb.OpenRecordset("tblRecordDate", dbOpenDynaset, dbSeeChanges)

Do While [frmReports]![StartDate] <= [frmReports]![EndDate]

Loop

For intCars = 1 To Me.tblRecordDate
rsReser.AddNew
rsReser("ReportDay") = [frmReports]![StartDate]

rsReser.Update
Next ReportDay

Set rsReser = Nothing
Dim stDocName As String
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Interesting QUERY problem
Replies
11
Views
530
mloucel
mloucel
T
Back End Read-Only Issue
2
Replies
26
Views
1,959
Gasman
Gasman
G
Filtering on Date fields problem
Replies
7
Views
1,178
azhar2006
A
C
limit date formula as variable
Replies
16
Views
1,355
MajP
MajP
S
Need to create an alias for the totals of a field in query
Replies
3
Views
858
sheckay
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom