Need a little help here, I am trying to create a custom counter for a database I am creating. Basically the customer wants a serial number for each record formatted by year then sequentially from 101 and so on. IE 12-101, 12-102 and resets each year IE 13-101, 13-102.

So I created this formula

CurYear = Format(Now(), "yy")
CurCount = DCount("[ID]", "Master", "left([sample_number],2)=CurYear")

If CurCount < 1 Then
   x = CurCount + 101
   SAMPLE_NUMBER = CurYear & "-" & x

ElseIf CurCount = 1 Then
   x = CurCount + 101
   SAMPLE_NUMBER = CurYear & "-" & x

Else
   x = CurCount + 101
   SAMPLE_NUMBER = CurYear & "-" & x
End If

The problem I am having is dcount is ignoring the variable CurYear and just counts all the records.

DCount("[ID]", "Master", "left([sample_number],2)=CurYear")

is there a way to use Curyear as a variable in that position or do I have to approach this from a different way?

So long story short I was trying to count only the records that matched the current year and add 101 to it. then Concatenate them to the formula 12-100 + etc

Any suggestions would be great help.

Gary

---

Reply from Richard Rost:

Try this:

CurCount = DCount("[ID]", "Master", "left([sample_number],2)='" & CurYear & "'")

Access will convert it to:

CurCount = DCount("[ID]", "Master", "left([sample_number],2)='12'")

We talked about this in the first DLOOKUP class, I think. Also, you do realize that your numbering scheme is going to run into problems in the year 2100. Just sayin'. :)