incremental number

[Raul Sousa]

I want to set a field (docId) value to be equal to: two Letters + year +
incremental number.
Ex: VD20061; VD20062; VD20063

I have the fowling code for the letter and for the year:
DocID = "VD " & Year(Now())

I can not, unfortunately, build the code to the incremental number.

Anyone can help me here?

 

[Allen Browne]

You will need to use a fixed width number at the end, say 3 digits like
this: VD2006001, VD2006002, ...

Dim varMax as Variant

DocID = "VD" & Year(Date)
varMax = DMax("DocID", "Table1", "DocID Like """ & DocID & "*""")
varMax = Val(Nz(Mid(varMax, 7), 0)) + 1
DocID =DocID & Format(varMax, "000")

 

[Jeff L]

DocID = "VD" & Year(Now()) & (DCount("DocID", "YourTableName",
"Left(DocID,6) = '" & "VD" & Year(Now()) & "'") + 1)

What this code will do is increment your records for 2006....1,2,3,4,5,
etc and then for 2007 start over with 1. If you don't want to start
over with 1 then change the DCount part to (DCount("DocID",
"YourTableName") + 1)

Hope that helps!

 

[Klatuu]

I would not recommend the DCount for this situation. Assume the users enters
10 records. Now the user deletes DociD 4. The highest value is 10, but
DCount will count only 9, return a 10, and fail with a key violation. I am
sure that is why Allen Browne recommends the DMax.

 

[Jeff L]

Good point Klatuu.

I would not recommend the DCount for this situation. Assume the users enters
10 records. Now the user deletes DociD 4. The highest value is 10, but
DCount will count only 9, return a 10, and fail with a key violation. I am
sure that is why Allen Browne recommends the DMax.

 

[Raul Sousa]

Thank you all for your reply.
They did fill my questions and helped me.


"Raul Sousa" escreveu: