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]

Could you "be kind and provide a sample" list of records so we have an understanding of what you mean by "product changes"?

Your question suggests there is some order to the records in your report. Can you share anything more?

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]

 

[maxhugen]

You've asked this previously, and I and others have provided ways to do this. However, I can't find your original post using the forum search...

Max Hugen
Australia

 

[MajP]

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

just call this function and pass the table/query or relevent sql string and the desired field name.

If I understand you correctly

boat
boat
boat
plane
car
plane
boat

This returns four (boat to plane to car to plane to boat)

 

[hshaker]

Hi there,

Here is the the paramters that I am passing to the function

=getNumberChanges([queryProductionRunsBetween2Dates],[Product])

Now when I open the form, it asks to enter a paramter value.

Any help?

 

[hshaker]

I get an error!

In Product Changeover field I get an error.

Any help.

 

[hshaker]

When I pass tblProducts then I will always get value "14" no matter what dates i enter for the report.

And when I pass the query or the report, I will get an error.

Any help please?

 

[dhookom]

You can add some code to your report like:

Option Compare Database
Option Explicit
Dim strAllProducts As String
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If InStr(1, strAllProducts, Me.Product) = 0 Then
        strAllProducts = strAllProducts & "~" & Me.Product
    End If
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
    'txtProductCount is an unbound text box in the report footer
    Me.txtProductCount = Len(strAllProducts) - Len(Replace(strAllProducts, "~", ""))
End Sub

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]

 

[MajP]

Your error is in how you are passing the parmateters:

=getNumberChanges([queryProductionRunsBetween2Dates],[Product])

Not sure what the square brackets are for unless you made a typo. The arguments are defined as strings.

Public Function getNumberChanges(strDomain As String, strField As String) As Integer

Therefore:
=getNumberChanges("queryProductionRunsBetween2Dates","Product")

Try calling this function from the immediate window prior to using it in a report or form, to ensure you know how to call the function.

 

[hshaker]

MajP

Here is the code that I have:

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


And Here is how I am calling the function:

=getNumberChanges("queryProductionRunsBetween2Dates","Product")

This does not work
In immediate error: compile error:
Expected line number or end of statement.

 

[dhookom]

Does the query "queryProductionRunsBetween2Dates" use parameters? Also, I believe the getNumberChanges() function will only work if the domain is ordered by the strField.

Did you try my suggestion?

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]

 

[TheAceMan1]

How are ya hshaker . . .

When you call [blue]dhookom's[/blue] function you should do so from the report passing the [blue]recordsource[/blue] since this is the actual used.

Put an unbound textbox in the header or footer of the report and set its [blue]controlsource[/blue] to ([purple]and exactly as you see it[/purple]):

[blue]=getNumberChanges(Me.RecordSource,"Product")[/blue]

Then check it out.

Now as [blue]dhookom's[/blue] is trying to point out to you, the count you receive depends on wether the recordsource of the form is sorted or not. Say you have the following unsorted list:
[blue]Pop
Eye
Doyle
Pop
Eye
Doyle
Pop
Eye
Doyle[/blue]
This is of course a count of 8. However if you sort the list (AKA sort the recordsource of the report):
[blue]Doyle
Doyle
Doyle
Eye
Eye
Eye
Pop
Pop
Pop[/blue]
With a resulting count of 2.

It all depends on how you present your data to the function. Either way the function is successful.

Hope this helps! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[MajP]

Looks like you are calling the function correctly and you copied the function correctly. Can you post the exact error message and the line that gets highlighted? The error your describing is usually caused when you leave a set of parentheses open. I wrote it as a function so that you can call it from anywhere and call any query and any field.

 

[dhookom]

=getNumberChanges(...) is not my creation and I don't think it will work for 2 reasons:
[li] it requires the field being counted is sorted. It doesn't appear that this is the case. If the field was sorted then don't use any code since a running sum in a header would work fine[/li]
[li] The query passed in to the function will not consider any where condition applied in the DoCmd.OpenReport code[/li]

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]

 

[TheAceMan1]

Howdy MajP . . .

Apologies for misquoting the originator of the code

[blue]hshaker[/blue] . . . in my prior post I was refering to the code posted by [blue]MajP![/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[MajP]

dhookum,
The function works fine and not sure what suggests that the product field needs to be sorted, it most definately does not. It simply reads the first record sets the value to a varTemp, reads the next record if varTemp (value of previous record) does not equal the value of the new record then it increments the counter., but the sort order determines the number of changes as AceMan points out. I would assume they are sorted sequentially by time, maybe when they were ordered or purchased.

like to know how many times a product changes from one to another. This is different from how many different products there are.

Day Product Person
1/1/2007 Car Mike
1/2/2007 Boat Bob
1/3/2007 Car Bob
1/4/2007 Car Bob
1/5/2007 Bike Mike
1/6/2007 Car Bob

As AceMan points out the answer is 4 although there are only three products.

Also what suggests that it would not handle a where condition? The recordsource is the recordsource.

 

[dhookom]

MajP,
My bad on the counting unique vs counting changes. I am posting to too many different threads.

However, consider what would happen with your code if the report was opened like:

Dim strWhere as String
strWhere = "[DateField] BETWEEN #1/3/2007# AND #1/5/2007#"
DoCmd.OpenReport "rptProdPerson", acPreview, , strWhere

The actual number of changes in the report would be 2, not 4.

I would modify your code within the report's on print event of the detail section. You would need to set up the

  Dim intCounter As Integer
  Dim varTemp As Variant

in the report modules general declarations section. Use the report's filtered recordsource rather than creating a DAO recordset.

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]

Hi there,

I would like only MajP to answer my question. MajP function works and makes sense to me. Please no one else post anything, otherwise this would get really confusing.

Here is the error MajP when I pass the function in immediate window and press enter:

Compile Error:

Expected: Line number or label or statement or end of statement.

tHANKS MajP

 

[hshaker]

Hi MajP,

Although I get that error in immediate window, I don't have the problem running the report and I always get 14 between any two dates.

Are you sure the function is correct?

 

[hshaker]

Majp never mind the previous thread. Please look at the one before.

When I pass tblProduct i always get 14

When I pass query like I mentioned i get error on report and on immediate window i get the error that I posted earlier.