Increment the number

[emmim44]

Hi fellows,
I am trying increment the confirmation number based on the current date for each new request...It does increment in the sub form...but when I select username or add a new entry to the main form, it doesnt increment the number ...Means that the code should get the max number from table then increment for the same day for each some event click or select on the main table...Your help is great...
Example:
Todays first confirmation should be 11150701...11150702...




Private Sub Form_Current()
Dim newvalue As String, rs As Object, zeroes As String, old As String, L As String
Set rs = Me.RecordsetClone
If Me.NewRecord Then
If rs.RecordCount = 0 Then
Me.confnum = Format(Now, "mmddyy") & "01"
'MsgBox Me.confnum
Else

rs.MoveLast
old = Left(rs!confnum, 6)

If DateDiff("d", Format(Now, "mmddyy"), old) = 0 Then
L = Right(rs!confnum, Len(rs!confnum) - 6) + 1
If Len(L) = 1 Then
L = "0" & L
End If
Me.confnum = Format(Now, "mmddyy") & L
Else
Me.confnum = Format(Now, "mmddyy") & "01"
End If

End If
End If
rs.Close
Set rs = Nothing
End Sub

[akn112]

wouldnt u want ur conf number to be formatted as "yyyymmdd" & ##? this way u can sort by conf number and it will be sorted by request date.

this way, u can check if there has been an entry for today's date

if format(date(),"yyyymmdd") = format(left(myDate,6), "yyyymmdd") then
confnum= format(date(),"yyyymmdd") & "01"
else
confnum= dmax("[Field]","tblTable") +1
end if

[emmim44]

The date format should be mmddyy ...

[akn112]

then i would suggest

confnum= dmax("right([Field],length)","tblTable", left([Field],6)=format(date),"mmddyyyy")) +1

[emmim44]

But when I select a different person from the main form. it doesnt get the latest number for the current date ...it starts with mmddyy01...why?

[akn112]

From looking at your code, i believe there's two possibilities where the error is from. It could be ur recordset is not being pulled the way u believe it to be, or ur old var might not be what u think it is.

Firstly i would add some code to open your table so u can view it. This way u know your recordset is good. If that passes, then insert a break in front of where u have ur datediff function. When it breaks check ur immediate window to see what ur old variable is.(IE: print old, print DateDiff("d", Format(Now, "mmddyy"), old)). If you can post a sanitized version, it might help me test out a few things.

[DarkSprout]

mmddyy: will not provide you with a guarantied high number when read from a Table/Value List

122007 (December 20th 2007) is a higher number then One Month Later
012008 (January 20th 2008)

Where as:

yyyymmdd:=
20071220 (2007 December 20th)
20080120 (2008 January 20th) is guarantied to provide the newest date as the highest number

So,


Public Function IncTableUID() As Double
'return highest value in table 'Plus 1' using the date format
'YYYYMMDD as a unique code
Dim nNewvalue As Double

nNewvalue = DMax("ID_Field", "TableName")
If InStr(1, str(nNewvalue), Format(Date, "yyyymmdd"), vbTextCompare) = 0 Then
nNewvalue = Val(str(Format(Date, "yyyymmdd")) & "01") ' will give you 99 unique entries p/day
'(or add "001" for 999 entries)
Else
nNewvalue = nNewvalue + 1
End If
IncTableUID = nNewvalue
End Function

[emmim44]

As I mentioned, I need that format...because all the other projects use that format...mmddyyyy

[OTWarrior]

Why not convert your numbers into the format that the above code could use to put them into order, then convert the numbers back. That way they could be in the right order and in the format you require.

That code looks like it gives you a Unique ID, so you could use that for sorting whilst the values you need are in your specified format.

You need the numbers to finish in your required format, but until then you can easily chop and change the order to get what you require:

eg:
Stage 1: mmddyyyy001
stage 2: yyyymmdd001
stage 3: yyyymmdd001 - 01 'sort into order and add index
stage 4: mmddyyyy001 - 01 'convert back into chosen format, sort via index number

hope that helps