Calculating Values for Records in a Report

[Rahz]

Hi all, I am stuck with a problem.

Up til now, I have been able to crawl my way around the web to find answers to my coding needs to complete tasks. This particular issue however is probably way simpler than I probably think, but am unable to resolve it.

The Scope:
I have built an invoice system for my current place of employment, that is mostly automated. The latest request however is to generate a report based on a specific time frame, that displays equipment usage and associated costs. For the most part, the report works as desired - except for the final part, which is calculating the % of usage based on the actual time available, and the time used by the equipment within the specified dates. The problem is, the form does not know what dates are selected, so it has to be calculated on the fly with the report. And I do not know how to get these individual values for a specific row within the report.

Ex. Pick a Date Range (we'll use 8/28/2016 to 9/28/2016),

Query Loads --> Loads Report --> Date Pick Form Closed


2 Records are found,

Station Duration Available Percent Cost
1 1028 1728 40% $13,925.91
2 696 1728 40% $8,519.09



The Problem: The On the Fly coding (See below) takes only the Last recordset Duration, and divides it by the Available to get the 40% value. This same value is used against all the rows. I want to use each individual row Duration divided by Available to get individual Percentages. Here is the code.

strSQL = "SELECT * FROM T1"
Set rs = CurrentDb.OpenRecordset(strSQL)

'Checking for Records
If Not rs.EOF And Not rs.BOF Then
    'Move to First Record
    rs.MoveFirst
    
    'Because First Record is 0 (No Station), we want to omit it
    rs.MoveNext
    
    'Move through the Records until EOF is met
    Do While Not rs.EOF
        Me!hoursPercent.value = Me!SumOfglobalDuration.value / Me!hoursAvail.value
        
        'To prevent an Infinite Loop, as the RecordCount should never be greater than 8
        subCount = subCount + 1
        If subCount = 10 Then
            GoTo Exit_Handler
        End If
        'Goto Next Record
        rs.MoveNext
    Loop

Again, all the math works....it just that the SumOfglobalDuration is only for the last record, and then used for all the other records. How can I fix this? Please help and thanks.

 

[pbaldy]

For starters, the bit to prevent an infinite loop shouldn't be necessary. It's forgetting the MoveNext that usually causes that. Where is this code (what event)? I would probably do this by having a textbox in the report footer that sums the duration, and have a textbox in the detail that divides that record's duration by that textbox amount.

 

[Rahz]

For starters, the bit to prevent an infinite loop shouldn't be necessary. It's forgetting the MoveNext that usually causes that. Where is this code (what event)? I would probably do this by having a textbox in the report footer that sums the duration, and have a textbox in the detail that divides that record's duration by that textbox amount.

Thanks pbaldy for replying,

The prevent infinite loop if statement I used because I did forget the movenext during the "test run" of the report, and it was annoying, so I just put that in their for redundancy purposes.

So the event is the OnLoad Event of the Report.

I put the total available hours on the footer, and then divided the sumofduration by that to get the percent, but the same problem still occurs.

 

[pbaldy]

Code in the load event will only run once, so you'd certainly get the same value for every record. To get record-specific results, you'd need code in the detail format event. That said, the suggestion to use a total in the report footer should work (just tested). In the detail section you'd have at the least:

=FieldName/TextboxName

though I'd probably add the Round() function. Can you attach the db here?

 

[Rahz]

I have attached the database. Please forgive the optimization, I still have a lot to do with it, as it is currently in "Alpha" state.

 

[pbaldy]

Not sure if txt_TAH was your attempt. If so, it needs to be in the report footer, not the page footer, and needs to sum. I moved it and made it:

=Sum([SumOfglobalDuration])

and put this in the percent of usage textbox in the detail section:

=[SumOfglobalDuration]/[txt_TAH]

 

[Rahz]

I just followed your last post, and it worked! Thank you. I have literally sat here and beat my head over this for an entire day. So I don't even need all the fancy rs jargon. Thanks!

 

[pbaldy]

Happy to help! Correct, I commented out all the recordset stuff when I tested.

 

[Rahz]

While just going through, and using the main form on the database, I noticed something wrong.

I have this set in the Form Current Event,

'Creating the Form OnCurrent Event for automating invoice ID
    pDate = Right(Date, 2)
    setPre = pDate
'Initiating invoiceID, assigning 0's based on number of ID

Select Case ID
    Case Is < 10
    invoiceID = setPre & "00" & ID
    invoiceTxt = invoiceID
    Case Is > 9
    invoiceID = setPre & "0" & ID
    invoiceTxt = invoiceID
    Case Is > 99
    invoiceID = setPre & ID
    invoiceTxt = invoiceID
End Select

but when ID is greater than 99, there is still a 0 being placed in the InvoiceID..... This should not be happening.

so Below 10,
16001
Above 9,
16011
and Above 99
16111

but for some reason it is still showing 160111, why is this so?

 

[pbaldy]

They are evaluated in order, and the first met is used. Greater than 99 meets this test:

Case Is > 9

 

[Rahz]

Took a smoke break, and figured it out. So this is the update, and it works as desired.

'Creating the Form OnCurrent Event for automating invoice ID
    pDate = Right(Date, 2)
    setPre = pDate
    Dim pseudoID As Integer
'Initiating invoiceID, assigning 0's based on number of ID
If ID > 0 And ID < 10 Then
    pseudoID = 1
ElseIf ID > 9 And ID < 100 Then
    pseudoID = 2
ElseIf ID > 99 Then
    pseudoID = 3
End If

Select Case pseudoID
    Case 1
    invoiceID = setPre & "00" & ID
    Case 2
    invoiceID = setPre & "0" & ID
    Case 3
    invoiceID = setPre & ID
End Select

Again, thanks a lot for the help! You have saved me from scrapping this project.

 

[pbaldy]

You can do this type of thing:

Case Is < 10
Case Is < 100
Case Else

You don't have to test for > 9 in the second test, you've already eliminated anything less than 10 with the first test.