Count help 1

[pd06498]

I am trying to use the below code to count the number of files in a year, so that each time I add a new file (by adding the reported date) the database will allocate the next number in sequence.

Private Sub ReceiveDate_AfterUpdate()
Me.YearID = DatePart("yyyy", [ReceiveDate])
Me!LocalFileID = Nz(DMax("[LocalFileID]", "tblCrashFile", "[YearID]=" & Me.YearID), 0) + 1
Me.LocalNumber = [LocalFileID] & "/" & [YearID]
Me.CrashRecordsDate = Now()
End Sub

My problem is that when I reach 10, all the new files following remain at 10, so I end up with a lot of 10/2005 files.

Can someone help?

 

[jebry]

Hi!

The problem is that LocalFileID is text so 9 > 10. To me the easiest thing to do is to format the field:

Me!LocalFileID = Format(Nz(DMax("[LocalFileID]", "tblCrashFile", "[YearID]=" & Me.YearID), 0) + 1, "000")

This assumes that you will have no more than 1000 files per year. If that isn't the case add another 0 to the format. If you will have 100 or less, take a 0 away.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[pd06498]

Thanks Jeff
Worked well...