Help
-
I am trying to get a number that will start at (2 digit month, 2 digit year (not visible) and 3 digit sequence number) that will automatically reset to 001 when the month changes. The number needs reference a table and go off the last number used. I have not been having any luck with it :banghead: any suggestions? Private Sub cmd1_Click() Set localConnection = CurrentDb() Set rJN = localConnection.OpenRecordset("TEST") rJN.MoveLast Dim cMonth As Integer Dim cYear As Integer Dim vYear As Integer Dim vMonth As Integer Dim vNum As Integer vYear = Int(Val(Left$(rJN![Test], 2))) vMonth = Int(Val(Mid$(rJN![Test], 2))) vNum = Int(Val(Right$(rJN![Test], 3))) cYear = Year(Now) cMonth = Month(Now) If vMonth = cMonth Then rJN = rJN + 1 Else rJN = "001" End If End Sub :help: Thanks
-
I am trying to get a number that will start at (2 digit month, 2 digit year (not visible) and 3 digit sequence number) that will automatically reset to 001 when the month changes. The number needs reference a table and go off the last number used. I have not been having any luck with it :banghead: any suggestions? Private Sub cmd1_Click() Set localConnection = CurrentDb() Set rJN = localConnection.OpenRecordset("TEST") rJN.MoveLast Dim cMonth As Integer Dim cYear As Integer Dim vYear As Integer Dim vMonth As Integer Dim vNum As Integer vYear = Int(Val(Left$(rJN![Test], 2))) vMonth = Int(Val(Mid$(rJN![Test], 2))) vNum = Int(Val(Right$(rJN![Test], 3))) cYear = Year(Now) cMonth = Month(Now) If vMonth = cMonth Then rJN = rJN + 1 Else rJN = "001" End If End Sub :help: Thanks
Set localConnection = CurrentDb() Set rJN = localConnection.OpenRecordset("TEST") rJN.MoveLast dim dtRS as Date = rJN![Test] If dtRS.Month = Now.Month Then dtRS.Month = dtRS.Month + 1 Else dtRS.Month = "01" End If debug.writeline (dtRS.ToString) Have not tested this...but may be this is what u want... Regards, NetPointer