Auto reference number code query

[Haynesey]

Hi, I use the following code to create automatic reference numbers like DHES:1, DHES:2, DHES :3 and so on. This works until it gets to DHES:10 but it now just keeps repeating itself. Any ideas?


If Me.CapitaID.OldValue = "" Or IsNull(Me.CapitaID.OldValue) Then
Me.CapitaID = "DHES:" & _
Format(Nz(DMax("Mid([EmailID], 6)", _
"tblHD", _
"[EmailID] Like '" & "DHES:" & "*'"), 0) + 1)
End If

Thanks in advance

Lee

 

[KenHigg]

Format(val(Nz(DMax("Mid([EmailID], 6)", "tblHD", "[EmailID] Like '" & "DHES:" & "*'"), 0)) + 1)

???
ken

 

[Pat Hartman]

Even better, don't store string and numeric values in the same field. You are also going to find that your generated ID sorts funny. DHES:10, DHES:11, DHES:12, DHES:13, DHES:14, DHES:15, DHES:16, DHES:17, DHES:18, and DHES:19 will all come between DHES:1 and DHES:2 which is probably not what you had in mind.