How can I make this function work

[Mohammed Abdul]

The basic logic to generate the next, sequential, TaskID Number, minus the missing critical piece of information listed previously in Post #3 would be something similar to:

Expand|Select|Wrap|Line Numbers
Public Function fGenerateNextTaskID() As String
'Task ID Format: YYYY-JJ-TT (Year - Job# - Task#)
Dim strLastTaskID As String
Dim intLastTaskIDYear As Integer
Dim intLastTaskIDJobNum As Integer
Dim intLastTaskIDTaskNum As Integer
 
Dim intNextTaskIDYear As Integer
Dim intNextTaskIDJobNum As Integer
Dim intNextTaskIDTaskNum As Integer
 
strLastTaskID = DLast("[TaskID]", "tblTasks")
 
intLastTaskIDYear = Val(Left$(strLastTaskID, 4))
intLastTaskIDJobNum = Val(Mid$(strLastTaskID, 6, 2))
intLastTaskIDTaskNum = Val(Right$(strLastTaskID, 2))
 
If Val(intLastTaskIDYear) = Year(Date) Then             'Not a New Year
  intNextTaskIDYear = intLastTaskIDYear
Else
  intNextTaskIDYear = intLastTaskIDYear + 1             'New Year, increment by 1
End If
 
'Missing information concerning Job Number
'If Job Number is the same
  intNextTaskIDJobNum = intLastTaskIDJobNum             'maintain Current Job#
  intNextTaskIDTaskNum = intLastTaskIDTaskNum + 1       'increment Task#
'Else
  intNextTaskIDJobNum = intLastTaskIDJobNum + 1         'increment Current Job#
  intNextTaskIDTaskNum = 1                              'Reset Task# to 1
'End If
 
fGenerateNextTaskID = CStr(intNextTaskIDYear) + "-" & Format$(intNextTaskIDJobNum, "00") & _
                      "-" & Format$(intNextTaskIDTaskNum, "00")
End Function

NOTE: This code requires a strick adherence to the TaskID Format of: YYYY-JJ-TT and a seeding of the 1st ID, meaning that you need only a single, manually entered ID, for the code to work.

[orangeCat]

Personally, I think you are trying to put too much intelligence into the TaskId. Use a date field for a Date.

One field one fact. Keep it simple.

[ADezii]

What exactly are you comparing the Last Task ID Job Number to? I'm a little confused on this point.

[Mohammed Abdul]

Thanks Guys! Adezii, this is your code mentioned in one of the queries about custom primary/ID numbers, I was just trying to use the same code to suite my situation.
I created DB for accident/incident tracking, I have more than one recommendation for every event, I have two forms one is parent form which has control source from EventTbl n has EventID; I do have sub form which records recommendations for each event and they r more than one recommendation for each event.
I also want that as the year changes the Event ID shall start from 1 and for every eventID the recommendationI D shall start with 1,2,3.

What I need is following:
1. The EventID shall be this format: Year-ASD-EventID#.
2. The Recommendation ID shall be Year-ASD-EventID#-RecID#

With the new year change the Event# shall reset to 1 and for every EventID the recommendation ID shall start with 1
example: ASD-0123-2011 ( EVENTID)
ASD-0123-2011-01 (RECID)
ASD-0123-2011-02 (RECID)
ASD-0123-2011-03 (RECID)

Anyone can help me please.

[ADezii]

The Logic, as I see it, is as follows:

1. For a given EventID, check and see if any RecIDs exist.
2. If a RecID does not exist for the EventID, concatenate '-01' to the EventID to generate the 1st RecID (Code Line #7).
3. If a RecID(s) exist for a given EventID, find the Last RecID, and then increment the last 2 Digits in the RecID String in order to obtain the next RecID in sequence (Code Lines 11, 12).
4. in Code this would look like:
   

   Const conEVENT_ID As String = "ASD-0123-2011"
   Dim strRecID As String
   Dim strLastRecID
   
   'Is there a RecID for a given Event? If not, create a New Rec_ID from the EventID
   If DCount("[RecID]", "tblRecIDs", "Left$([RecID],13) = '" & conEVENT_ID & "'") = 0 Then
     strRecID = conEVENT_ID & "-01"
   Else
     'A RecID exists for a given EventID, so increment the RecID Component by retrieving the
     'Last RecID for the given EventID
     strLastRecID = DLast("[RecID]", "tblRecIDs", "Left$([RecID],13) = '" & conEVENT_ID & "'")
     strRecID = Left$(strLastRecID, 14) & Format$(Val(Right$(strLastRecID, 2)) + 1, "00")
   End If
   
   Debug.Print strRecID

[NeoPa]

This question is one I would have deleted had it not already garnered a number of replies. It's poorly asked with an unacceptable title. The other thread that it's a repost of can be found at Custom Primary ID (not Autonumbers). The best I can do with this now is lock it to further posting and lament the time and effort wasted.

There's not even enough good information here to know what the title should be.