Microsoft Access - RUNNING DATES COUNTER

Hi to all,

i have two field name production_date and dates_replace. what i want is, when dates replace there will be a counter recognized that the dates was changes and the counter start counting
Example:

Production Date  Date Replace    Counter
Jan.1 2012  Jan. 1, 2012   1
Jan.2, 2012   Jan. 1, 2012   1
Jan 3, 2012   Jan 3, 2012   2
Jan 4, 2012   Jan. 3, 2012    2
Jan 5, 2012   Jan 3, 2012   2
Jan 6, 2012   Jan 6, 2012   3

and so forth.

I dont have any idea if this will be posible. Please help me. Thanks.

Do you want to update this in a table, or have it come out in a report/query?

i want to update on my query or report or on report only.

This is something you'll need to do in the report.

Create a global variable in the report's class module.

Public gCounter as Integer

Initialize it in the report's Open event.
gCounter = 0

Increment it in the detail section's Format event.

If Me.ProductionDate = Me.ReplaceDate Then
  gCounter = gCounter + 1
End If

Back out the increment in the Retreat event.
If Me.ProductionDate = Me.ReplaceDate Then
   gCounter = gCounter - 1
End If

Access formats each line and then determines if there is room on the current page to print it.  If Access needs to change the page first, it runs the Retreat event giving you the opportunity to "undo" any update you did in the Format event.  It then prints the page footers, headers for the next page and finally runs the Format event for the current record a second time.

In most SQL implementations you could select using the aggregate function count(distinct user). But Access doesn't support that construct. I think the best you could do is to select the distinct values in a subquery and count them.

I was going to write a query but this
http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx seems to do a good job.


even you can refer to the below link also,

http://support.microsoft.com/kb/210276

Hope it helps you.

hi,

I think you should create trigger to updated value in table.  you will have to create trigger like this


hope this will help you