troubleshooting Question

MS Access Report first record on new page being counted twice.

Avatar of AronMcD
AronMcD asked on
Microsoft AccessMicrosoft Development
6 Comments1 Solution696 ViewsLast Modified:
I have a report that I'm using to determine on-time delivery percentage.  What I'm doing is subtracting the Required by Date by the Actual Finish date.  I then have an icount variable that i increment by 1 when the subtraction listed above equals zero or higher (indicating the trailer was delivered on time).  The problem I'm having is that for some reason the first record on a new page is being "looked at" if the subtraction is zero or greater it's being counted twice.  This is obviously affecting my percentage calculation.  Here is my code (in the detail section)

Option Compare Database
Option Explicit

Private iCount As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Nz(Me.txtMustHave.Value, 0) = 0 Or Nz(Me.txtActualFinDate.Value, 0) = 0 Then
        Me.txtPromiseDelivery.Value = Null
        Me.txtPromiseDelivery.Value = DateDiff("d", Nz([FinCompleteDate], 0), Nz([RequiredBy], 0))
    End If

    If Me.txtPromiseDelivery.Value >= 0 Then
        iCount = iCount + 1            
    End If    

End Sub

Private Function getOTDCount() As Double
    getOTDCount = iCount
End Function

In the Report Footer section of my report I have a text box doing the % calculation with the control source set to: =getOTDCount()/Count([JobNo])

Has anyone run into this problem before?  How can I fix this?  Or is there a better way to do what I'm attempting to do here?  

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros