Hi, can anyone help? I currently have a database which creates a unique sequential number for each new donor I receive. The current number pulls the year from date() and adds 001 etc. to give a number such as 2017-001 I need to increase the numbers to a sequence on 12 but if I change anything in the code below it stops working
The code runs on opening the form by being placed in the "on Current" function I would be quite happy if I could just pad out the space between year and number with 0's eg
201700000123
can anyone help?
The code runs on opening the form by being placed in the "on Current" function I would be quite happy if I could just pad out the space between year and number with 0's eg
201700000123
can anyone help?
Code:
Dim x As Long, ss As String
If Me.NewRecord Then
ss = Nz(DMax("DonorNo", "Donor"), "")
If ss = "" Then
x = 0
Else
x = CLng(Left(ss, 4))
End If
If x <> Year(Date) Then
Me.DonorNo = Year(Date) & "-001"
Else
x = InStr(ss, "-") + 1
ss = Mid(ss, x, Len(ss))
x = CLng(ss) + 1
Me.DonorNo = Year(Date) & "-" & Trim(Right(Str(10 ^ 3 + x), 3))
End If
End If