how do i count each record based on date

[Guest]

I need to have a number automatically created utilizing each record added to
the year. I have it working now (based off the current reord plus the last
two of the year) but the problem is I need it to start over at the end of
each year. It should not be this difficult but I am stumped! We need to keep
track of how many requests we have for each year. A user enters the data
(could be many times an hour) then this is stored in the table. I am using
ACCESS 2003.

 

[Jeff Boyce]

Mike

It sounds like you want to use a number to "count", and to set up a field in
a table, and ....

How about just using a query to count the annual number of requests where
Year(YourDateField) = 2006? (or whatever)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the quick response.
Yeah...I thought of that but I need it to be dynamic. Here is what I have:
1.) A request comes in from the field for work to be done
2.) Engineer receives the order and if they approve then a number is
assigned to that request
3.) When they put in the date that the request is approved I take the last
two numbers of the year adding it to the record number
4.) This is what we call an ERS number
5.) Right now my number looks like this: 1.2006, 2.2006, 3.2006
6.) That number has to increment up to the end of each year starting over
with one (i.e. 1.2005, 1.2006, 2.2006)
7.) If the engineer doesn't approve it then it doesn't get a number but is
still stored in the database table

If I can have a number automatically be entered into a field when the
request has been approved (APPROVED_NUMBER) and then attach it to the year
but reset it to one when the year changes that would be good. I even thought
of basing it on an hour/minute/day along with the year. I thought of the hour
but more than one may be entered during that time.
I'm stumped....
Maybe it will come to me this weekend when I'm not thinking of it.

 

[Jeff Boyce]

By definition, a query IS dynamic.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[j_beverly]

Mike,

I am an amateur, so this may or may not be the best way, but I was
intrigued by your question and here is what I came up with:

I would create a table to store the next approval number. The table
would have 2 fields: a sequence number and the year.
TableApprovalNumber
Field Name: Sequence Data Type: Number
Field Name: Year Data Type: Number

The table would always have only one record which will have the next
approval number to be assigned to an approved request.

On a form for entering/updating requests, I would have a command
button or checkbox called "Approved".
Cllicking on "Approved" would first compare the current year with the
year in TableApprovalNumber. If the current year is greater, then
TableApprovalNumber.Year is set to current year and Sequence is reset
to 1.

Then I concatenate the Sequence and Year to form the Approval Number.
Lastly, the Sequence is incremented so is ready for the next request
approval. Here is my code:

Private Sub Approved_AfterUpdate()
If Approved Then

Dim y As Integer 'year
Dim s As Integer 'sequence
Dim thisyear As Integer


y = DFirst("[Year]", "TableApprovalNumber")
thisyear = Format(Date, "yyyy")

'If this is the first number of a new year, reset the sequence to 1
If thisyear > y Then
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("UPDATE TableApprovalNumber SET
TableApprovalNumber.Sequence = 1, TableApprovalNumber.[Year] = " &
thisyear & ";")
DoCmd.SetWarnings (True)
y = thisyear
End If

'Get approval number for this project
s = DFirst("[Sequence]", "TableApprovalNumber")
'Concatenate Sequence & Year to form Aproval Number
ApprovalNumber = Str(s) & "." & Str(y)

'Increment the sequence number in TableApprovalNumber
DoCmd.SetWarnings (False)
DoCmd.RunSQL ("UPDATE TableApprovalNumber SET
TableApprovalNumber.Sequence = [Sequence]+1;")
DoCmd.SetWarnings (True)

End If 'If approved
End Sub

 

[Guest]

Yep...that worked. I must have had a brain freeze. Don't know why that one
was so difficult for me. Thanks for the help!