By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,186 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

how to break previous number in table

P: 8
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...
Dec 21 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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 :
    Expand|Select|Wrap|Line Numbers
    1. 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.
    Expand|Select|Wrap|Line Numbers
    1. strMaxKey = DMax("[Index]", "[Table]", "[Index] Like strStub & '*'")
  3. Set your new index field to the next one in the sequence.
    Expand|Select|Wrap|Line Numbers
    1. 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.
Dec 21 '07 #2

P: 8
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.
Dec 22 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 22 '07 #4

P: 8
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.
Dec 22 '07 #5

ADezii
Expert 5K+
P: 8,597
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).
Expand|Select|Wrap|Line Numbers
  1. Public Function fGenerateNextComplaint() As String
  2. Dim strMonth As String, strYear As String
  3. Dim strLastComplaintNum As String, strLastComplaintMon As String
  4. Dim strLastComplaintCounter As String
  5.  
  6. 'Return the last entered Complaint Number
  7. strLastComplaintNum = DLast("[Complaint_#]", "register")
  8.  
  9. strLastComplaintMon = Left$(strLastComplaintNum, 3)         'Returns Jan, Feb, Mar, etc.
  10. strLastComplaintCounter = Right$(strLastComplaintNum, 4)    'Returns 0001, 0023, 0134, etc.
  11.  
  12. Select Case strLastComplaintMon     'Return the Month of the 'Last' Complaint
  13.   Case "Jan"
  14.     If Format$(Now(), "mmm") <> "Jan" Then      'a Month change, so Reset Counter and Month
  15.       fGenerateNextComplaint = Format$(Now(), "mmm") & "07" & "0001"
  16.     Else    'Month hasn't changed, so increment Counter only
  17.       fGenerateNextComplaint = "Jan07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
  18.     End If
  19.   Case "Dec"
  20.     If Format$(Now(), "mmm") <> "Dec" Then      'a Month/Year change, so Reset Counter/Month/Year
  21.       fGenerateNextComplaint = Format$(Now(), "mmm") & "08" & "0001"
  22.     Else    'Month hasn't changed, so increment Counter only
  23.       fGenerateNextComplaint = "Dec07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
  24.     End If
  25.   Case Else
  26.     Exit Function
  27. End Select
  28. 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
Dec 23 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 23 '07 #7

P: 8
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).
Expand|Select|Wrap|Line Numbers
  1. Public Function fGenerateNextComplaint() As String
  2. Dim strMonth As String, strYear As String
  3. Dim strLastComplaintNum As String, strLastComplaintMon As String
  4. Dim strLastComplaintCounter As String
  5.  
  6. 'Return the last entered Complaint Number
  7. strLastComplaintNum = DLast("[Complaint_#]", "register")
  8.  
  9. strLastComplaintMon = Left$(strLastComplaintNum, 3)         'Returns Jan, Feb, Mar, etc.
  10. strLastComplaintCounter = Right$(strLastComplaintNum, 4)    'Returns 0001, 0023, 0134, etc.
  11.  
  12. Select Case strLastComplaintMon     'Return the Month of the 'Last' Complaint
  13.   Case "Jan"
  14.     If Format$(Now(), "mmm") <> "Jan" Then      'a Month change, so Reset Counter and Month
  15.       fGenerateNextComplaint = Format$(Now(), "mmm") & "07" & "0001"
  16.     Else    'Month hasn't changed, so increment Counter only
  17.       fGenerateNextComplaint = "Jan07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
  18.     End If
  19.   Case "Dec"
  20.     If Format$(Now(), "mmm") <> "Dec" Then      'a Month/Year change, so Reset Counter/Month/Year
  21.       fGenerateNextComplaint = Format$(Now(), "mmm") & "08" & "0001"
  22.     Else    'Month hasn't changed, so increment Counter only
  23.       fGenerateNextComplaint = "Dec07" & Format$(Val(strLastComplaintCounter) + 1, "0000")
  24.     End If
  25.   Case Else
  26.     Exit Function
  27. End Select
  28. 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
Dec 27 '07 #8

ADezii
Expert 5K+
P: 8,597
Thanks. it works. thanks for help me
Glad we were able to assist you.
Dec 27 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.