How to count the changeover of a field?

[hshaker]

I have a report with a field called [product], I would like to know how many times a product changes from one to another. This is different from how many different products there are.

Could someone be kind and provide a sample code?

thanks alot

 

[dhookom]

Passing a table will always result in inconsistent results since while the report might have an order to the records, a table will not.

As I asked before, does queryProductionRunsBetween2Dates have any parameters? Can you please provide the SQL view of the query.

Also, as I stated earlier, the function will not be related to your report if you open the report with a Where Condition.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hshaker]

SELECT tblProductionRuns.ProductionRunID, tblProductionRuns.StartDate, tblProductionRuns.StartTime, [startdate]+[starttime] AS Start, DateAdd("h",-6,[Start]) AS StartAdjusted, tblProductionRuns.EndDate, tblProductionRuns.EndTime, [enddate]+[endtime] AS [End], DateAdd("h",-6,([end]-0.01)) AS EndAdjusted, tblProductionRuns.Product, tblProductionRuns.Length, tblProductionRuns.TotalPieces, tblProductionRuns.RejectedPieces, tblProductionRuns.GoodPieces, tblProductionRuns.GrossProduction, tblProductionRuns.NetProduction, [netproduction]/[grossproduction] AS Yield, tblProductionRuns.ScheduledTime, tblProductionRuns.UnplannedDowntime, tblProductionRuns.PlannedMaintenance, tblProductionRuns.Trial, ([scheduledtime]-[unplanneddowntime]-[plannedmaintenance])/([scheduledtime]-[plannedmaintenance]-[trial]) AS Availability, ([Length]*[TotalPieces])/([ScheduledTime]-[UnplannedDowntime]-[PlannedMaintenance]-[Trial]) AS LineSpeed, tblProductionRuns.LineSpeedActual, tblProductionRuns.Shift, tblProductionRuns.PlannedSpeed, ([LineSpeedActual]/[tblProductionRuns].[PlannedSpeed]) AS ActualSpeedPercentage
FROM tblProductionRuns INNER JOIN tblProducts ON tblProductionRuns.Product = tblProducts.Product
WHERE ((([startdate]+[starttime])>=([Forms]![formDailyProductionDataQuery]![StartDate]+6/24)) AND (([enddate]+[endtime])<=[Forms]![formDailyProductionDataQuery]![endDate]+1+6/24))
ORDER BY [startdate]+[starttime];

 

[dhookom]

Just as I thought, your query has a where clause that depends of controls on forms. I wouldn't even try to use that query in the function.

You could run code in the On Format or On Print event of the detail section as I suggested earlier.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hshaker]

You are suggesting me to put the same fucntion in the on Format of the report footer (in this case).

Can you actually put a fuction inside a sub?

Here is my code on Format propery:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Author: Hamed Shakeraneh
Public Function getNumberChanges(strDomain As String, strField As String) As Integer
Dim rs As DAO.Recordset
Dim intCounter As Integer
Dim varTemp As Variant
Set rs = CurrentDb.OpenRecordset(strDomain, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
varTemp = rs.Fields(strField)
rs.MoveNext
End If
Do While Not rs.EOF
If Not rs.Fields(strField) = varTemp Then
intCounter = intCounter + 1
varTemp = rs.Fields(strField)
End If
rs.MoveNext
Loop
getNumberChanges = intCounter
End Function
End Sub

 

[dhookom]

I was not suggesting putting the previous function in your report. Consider something like this:
- create a couple variables in the general declarations of your report module

  Dim intCounter As Integer
  Dim strAllProducts As Variant

- then add code to the detail section On Format like

  dim strProduct as String
  Dim intLenProduct as Integer
  strProduct = Me.txtProduct 'your bound text box
  intLenProduct = Len(strProduct)
  If Left(strAllProducts, intLenProduct) <> strProduct Then
     intCounter = intCounter + 1
     strAllProducts = strProduct & strAllProducts
  End If

- In the On Format event of the report footer section, use code like

   'assuming txtCountChanges is an unbound text box in the footer
   Me.txtCountChanges = intCounter

I haven't tested this completely but you should get the idea of what I am attempting to do.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hshaker]

Please explain what left function exacatly does?

Please also explain what the variant strAllProducts do? I mean you want to compare the current and the next product and if they are not equal increment the counter.


Thanks a lot.

 

[dhookom]

Left() extracts the left n characters of a string.
Left("the fox jumped",3) = "the"
The code I wrote adds each new product to the left of the string strAllProducts. The current Product is measured with Len() and compared with the left most characters of strAllProducts. If they aren't the same, the counter is incremented by 1 and the Product is added to the left of strAllProducts. For instance:
[tt]
Product strAllProducts intCounter
========= ===================== ==========
AB 1
AB AB 1
CD AB 2
EF CDAB 3
EF EFCDAB 3
AB EFCDAB 4
CD ABEFCDAB 5
CD CDABEFCDAB 5
[/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]