Microsoft Access - RUNNING DATES COUNTER - Asked By lloyd pabio on 19-Mar-12 03:02 PM

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

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.

wally eye replied to lloyd pabio on 19-Mar-12 05:47 PM
Do you want to update this in a table, or have it come out in a report/query?
lloyd pabio replied to wally eye on 19-Mar-12 10:43 PM
i want to update on my query or report or on report only.
Pat Hartman replied to lloyd pabio on 20-Mar-12 12:26 AM
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.

Somesh Yadav replied to lloyd pabio on 20-Mar-12 01:45 AM

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 seems to do a good job.

even you can refer to the below link also,

Hope it helps you.

Reena Jain replied to lloyd pabio on 20-Mar-12 02:21 AM

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

CREATE TRIGGER Insert_updatevalue ON table1
declare @counter int
select @counter= SELECT counter FROM updated
Update table2 set counter=@counter +1 where id=select id from updated


hope this will help you