how to break previous number in table

[JVG]

hi,
i am create complaint register in Ms- Access 2000
ther is i want complaint no. which is depend on month and year. for Eg. if the month is Janury and year is 2007 then my complaint number is start from JA070001, JA070002.....JA070020 and so on up to month end. but the problem is when month wil change my complaint no. again start from 01 eg. FB070001, FB070002...... and so on.
how to break the previous number in table.
help me...

[NeoPa]

Firstly, I don't know what abbreviations you're using for the months so I will use the standard three letter abbreviations. You can change this as you need.
When you need to create a new number you :

1. Determine the current month stub. Something like :
   

   strStub = Format(Date(), 'mmmyy')

2. Get the maximum currently used in your table. You don't tell us what that (or the field) is so I will use [Table] & [Index]. You will need to replace these with your own correct names.
   

   strMaxKey = DMax("[Index]", "[Table]", "[Index] Like strStub & '*'")

3. Set your new index field to the next one in the sequence.
   

   strIndex = strStub & Format(Val(Mid(strMaxKey, 6)), '00000')

Clearly, the Index MUST be formatted as a fixed length string. I've assumed 5 for the stub (better actually would be 7 - mmmyyyy) and another 5 for the number. I don't know what would be more appropriate for your data, but I expect 5 will be fine anyway.

[JVG]

thks but i dont undestand your coding.
ok i will explain.
my table name is register
field name is complaint_no and datatype is text.

i want complaint no. is JA070001
JA-Janury, FB-Feburary .......
07 is year, 00001 is use for starting no.
my next no. will be JA070002 & so on
but month will be change then my complaint no. is FB070001 . i.e again start from 1,2, and so on.

send me again whole coding and where it is write or tell me if there is create any extra field.

thanks.

[NeoPa]

thks but i dont undestand your coding.

So tell me what you don't understand and we can progress from there.

ok i will explain.
my table name is register
field name is complaint_no and datatype is text.

i want complaint no. is JA070001
JA-Janury, FB-Feburary .......
07 is year, 00001 is use for starting no.
my next no. will be JA070002 & so on
but month will be change then my complaint no. is FB070001 . i.e again start from 1,2, and so on.

Information is good, but it helps if it's full where necessary and accurate always.
JA & FB are the first two months. Bearing in mind this is a completely non-standard naming convention I have no way of knowing what any of the other months are. Not a problem as you'll be doing the coding for that anyway (see below).
Please try to post your information accurately. You say [comlaint_no] = JA070001 then say that 00001 is the sequential part of it. This may not seem very important to you but believe me, when you're trying to determine what someone means when they haven't expressed themselves very clearly, all clues have to be used. Wrong information can lead someone in the wrong direction, wasting the time that you are asking them to devote to your problem.

send me again whole coding and where it is write or tell me if there is create any extra field.

thanks.

We don't do full-code solutions here. That's not what the site is about.
We help YOU to code and/or learn how to code. We do not do a simple coding service for those who can't or won't try to do it for themselves.
I've given you the pointers you need to proceed. If there are any specifics you need further help with please feel free to ask. This may well include some code samples for illustration purposes that you may even find you can use, but it will not just be done for you.

Moving on, is it absolutely necessary to code the months as you have done with two-letter abbreviations? This will make your task a lot more fiddly (complicated). Using a predetermined three-letter abbreviation would be so much easier. If it is not possible to change this then let me know & I will give you some help with this extra task of determining the correct string from the current date. You will of course, have to provide the codes for all months in this case.

[JVG]

sorry if i westing your time.
but i am in trouble. so help me.

ok i will take 3 letters for month i.e JAN , FEB then what is the coding for increment the number i.e JAN070001, JAN070002 ..........
actully there is no limit, how many complaint are come in one month.
it may be maximum 150 complaint in one month i.e my last complaint no. may be JAN070150. but when next month will come then my complaint no. is again start from 0001, i.e FEB070001........

thanks.

[ADezii]

sorry if i westing your time.
but i am in trouble. so help me.

ok i will take 3 letters for month i.e JAN , FEB then what is the coding for increment the number i.e JAN070001, JAN070002 ..........
actully there is no limit, how many complaint are come in one month.
it may be maximum 150 complaint in one month i.e my last complaint no. may be JAN070150. but when next month will come then my complaint no. is again start from 0001, i.e FEB070001........

thanks.

Here is a little code snippet (Function) for Jan and Dec which will hopefully point you in the right direction and return the next logical Complaint Number. It would require strict adherence to the predefined format of: mmmyynnnn e.g. Dec070128. It would also require you to 'seed' the 1st Complaint Number (add it manually).

Public Function fGenerateNextComplaint() As String
Dim strMonth As String, strYear As String
Dim strLastComplaintNum As String, strLastComplaintMon As String
Dim strLastComplaintCounter As String
 
'Return the last entered Complaint Number
strLastComplaintNum = DLast("[Complaint_#]", "register")
 
strLastComplaintMon = Left$(strLastComplaintNum, 3)         'Returns Jan, Feb, Mar, etc.
strLastComplaintCounter = Right$(strLastComplaintNum, 4)    'Returns 0001, 0023, 0134, etc.
 
Select Case strLastComplaintMon     'Return the Month of the 'Last' Complaint
  Case "Jan"
    If Format$(Now(), "mmm") <> "Jan" Then      'a Month change, so Reset Counter and Month
      fGenerateNextComplaint = Format$(Now(), "mmm") & "07" & "0001"
    Else    'Month hasn't changed, so increment Counter only
      fGenerateNextComplaint = "Jan07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
    End If
  Case "Dec"
    If Format$(Now(), "mmm") <> "Dec" Then      'a Month/Year change, so Reset Counter/Month/Year
      fGenerateNextComplaint = Format$(Now(), "mmm") & "08" & "0001"
    Else    'Month hasn't changed, so increment Counter only
      fGenerateNextComplaint = "Dec07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
    End If
  Case Else
    Exit Function
End Select
End Function

P.S. - There is probably an easier method, but it is near bedtime and I can't think of it now. I'm sure someone else will come up with a better system

[NeoPa]

sorry if i westing your time.
but i am in trouble. so help me.
...

JVG, I have already posted the answers for you. You need to take that and do some work with it.
As I said before, I will explain anything you're having trouble with as long as you ask specifically. In short, the more you show you're prepared to do the work, the more help you will get here.

I will just add a replacement for the code in point #2 from my earlier post (#2) as I now realise that it didn't handle the first [complaint_id] of the month correctly.

strMaxKey = Nz(DMax("[complaint_no]", "[register]", "[complaint_no] Like strStub & '*'"), 0)

Point #3 should also be changed as it didn't handle adding 1 to what already exists in the table. Also, as it's now clear you're using four digits for the count of complaints in a month, the format string is changed. The new version of that code is :

strIndex = strStub & Format(Val(Mid(strMaxKey, 6)) + 1, '0000')

ok i will take 3 letters for month i.e JAN , FEB then what is the coding for increment the number i.e JAN070001, JAN070002 ..........
actully there is no limit, how many complaint are come in one month.
it may be maximum 150 complaint in one month i.e my last complaint no. may be JAN070150. but when next month will come then my complaint no. is again start from 0001, i.e FEB070001........

You are using four digits to handle the complaint number (the count bit) so there IS a limit. With four digits your maximum number is 9,999. If that's not enough, you need to consider changing the layout of the field to handle more digits.

[JVG]

Here is a little code snippet (Function) for Jan and Dec which will hopefully point you in the right direction and return the next logical Complaint Number. It would require strict adherence to the predefined format of: mmmyynnnn e.g. Dec070128. It would also require you to 'seed' the 1st Complaint Number (add it manually).

Public Function fGenerateNextComplaint() As String
Dim strMonth As String, strYear As String
Dim strLastComplaintNum As String, strLastComplaintMon As String
Dim strLastComplaintCounter As String
 
'Return the last entered Complaint Number
strLastComplaintNum = DLast("[Complaint_#]", "register")
 
strLastComplaintMon = Left$(strLastComplaintNum, 3)         'Returns Jan, Feb, Mar, etc.
strLastComplaintCounter = Right$(strLastComplaintNum, 4)    'Returns 0001, 0023, 0134, etc.
 
Select Case strLastComplaintMon     'Return the Month of the 'Last' Complaint
  Case "Jan"
    If Format$(Now(), "mmm") <> "Jan" Then      'a Month change, so Reset Counter and Month
      fGenerateNextComplaint = Format$(Now(), "mmm") & "07" & "0001"
    Else    'Month hasn't changed, so increment Counter only
      fGenerateNextComplaint = "Jan07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
    End If
  Case "Dec"
    If Format$(Now(), "mmm") <> "Dec" Then      'a Month/Year change, so Reset Counter/Month/Year
      fGenerateNextComplaint = Format$(Now(), "mmm") & "08" & "0001"
    Else    'Month hasn't changed, so increment Counter only
      fGenerateNextComplaint = "Dec07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
    End If
  Case Else
    Exit Function
End Select
End Function

P.S. - There is probably an easier method, but it is near bedtime and I can't think of it now. I'm sure someone else will come up with a better system

Thanks. it works. thanks for help me

[ADezii]

Thanks. it works. thanks for help me

Glad we were able to assist you.