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.
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.
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.
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.