Results 1 to 8 of 8
  1. #1
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155

    Regarding program


    I have a ID eg. A-00454 I want to increase it by one. After increment the ID should contain 5 digits in the number portion.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds like you require what is frequently called 'dmax+1'. google/bing to find out more. If you require further help, you will need to explain what happens with the 'A' - is it always A, can be a different letter, or more than one letter. Also need to explain how you increment - is this manual entry so records are added one at a time - or copy/paste or import multiple records at a time. And whether it is

    A1
    A2
    A3
    B1
    B2
    B3

    or

    A1
    A2
    B3
    A4
    B5
    B6

    In principle you will need to isolate the numeric part of the id and make it numeric and add 1 e.g.

    val(dmax("mid(myfield),3)","myTable","perhaps some criteria here for the alpha part of the ID"))+1

    However note that it is usually better to store the ID in its component parts and correct datatype - much easier to maintain and generally faster operation - and combine as and when required - e.g.

    alphacode & "-" & format(numericcode,"00000")

  3. #3
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by Ajax View Post
    sounds like you require what is frequently called 'dmax+1'. google/bing to find out more. If you require further help, you will need to explain what happens with the 'A' - is it always A, can be a different letter, or more than one letter. Also need to explain how you increment - is this manual entry so records are added one at a time - or copy/paste or import multiple records at a time. And whether it is

    A1
    A2
    A3
    B1
    B2
    B3

    or

    A1
    A2
    B3
    A4
    B5
    B6

    In principle you will need to isolate the numeric part of the id and make it numeric and add 1 e.g.

    val(dmax("mid(myfield),3)","myTable","perhaps some criteria here for the alpha part of the ID"))+1

    However note that it is usually better to store the ID in its component parts and correct datatype - much easier to maintain and generally faster operation - and combine as and when required - e.g.

    alphacode & "-" & format(numericcode,"00000")
    I tried with the following code,

    Dim num As String
    Dim fnum As String
    Dim lnum As String
    num = Me.List174
    fnum = Right(num, Len(num) - InStr(num, "-"))
    fnum = fnum + 1
    Do While Len(fnum) < 5
    fnum = "0" + fnum
    Loop
    lnum = "I-" + fnum
    Me.CR_NO = lnum
    Exit Sub
    Err_code:
    MsgBox Error$



    Is val() required?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Estimate the reasonable max number of digits your ID can ever have (otherwise you can never order your data by ID, as older and newer entries will be messed up). And remember, better you'll have an additional "0" in your ID, than you'll have to update all your tables containing values of ID with this additional "0" someday in future. In your example in opening post it is 7, so I'll continue with it.

    Code:
    Me.CR_NO = LEFT(Me.List174,LEN(Me.List174)-7) & RIGHT("0000000" & CStr(CLng(Right(Me.List174, 7))+1),7)

  5. #5
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by ArviLaanemets View Post
    Estimate the reasonable max number of digits your ID can ever have (otherwise you can never order your data by ID, as older and newer entries will be messed up). And remember, better you'll have an additional "0" in your ID, than you'll have to update all your tables containing values of ID with this additional "0" someday in future. In your example in opening post it is 7, so I'll continue with it.

    Code:
    Me.CR_NO = LEFT(Me.List174,LEN(Me.List174)-7) & RIGHT("0000000" & CStr(CLng(Right(Me.List174, 7))+1),7)
    I tried with the following code,

    Dim num As String
    Dim fnum As String
    Dim lnum As String
    num = Me.List174
    fnum = Right(num, Len(num) - InStr(num, "-"))
    fnum = fnum + 1
    Do While Len(fnum) < 5
    fnum = "0" + fnum
    Loop
    lnum = "I-" + fnum
    Me.CR_NO = lnum
    Exit Sub
    Err_code:
    MsgBox Error$



    Is val() required?

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    On based on this (your answer to Ajax) was my post.

    1. Why have a long procedure when a single formula will do?

    2. "fnum = fnum + 1" You are adding 1 to string! It will not work in Access (but it will work in Excel)!

    3. I used CLng() - it converts string to ńumber!

    4. You probably didn't notice the main reason for using fixed-length string ID! Here will be explanation by example:

    You have a ID in string format like in your opening post with un-fixed length. Let's assume this ID is Primary Key, i.e. records in your table are ordered by it. And let's assume the order is from smallest to largest.

    You add a 1st record:
    "A-1", ...;
    You add other records up to 9th:
    "A-1", ...
    "A-2", ...
    ...
    "A-9", ...
    You add 10th record:
    "A-1", ...
    "A-10", ...
    "A-2", ...
    ...
    "A-9", ...
    You add 11th record:
    "A-1", ...
    "A-10", ...
    "A-11", ...
    "A-2", ...
    ...
    "A-9", ...
    You add 101th record:
    "A-1", ...
    "A-10", ...
    "A-101", ...
    "A-11", ...
    "A-2", ...
    ...
    "A-9", ...

    Etc.

    And the For cycle in your code to add preceding zeros is moot, as with your current code until "A-99999" you get proper ID's, but after this all will continue as in my example. I.e. You get entries like (and no leading zeros, of-course)
    "A-100001", ...
    "A-1000010", ...
    "A-10000101", ...
    "A-1000011", ...
    "A-100002", ...
    ...
    "A-100009", ...

    And in case you set the For cycle with bigger length, as I adviced, then why to use a cycle, when a simple Right() formula will do?

  7. #7
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155

    Smile Regarding Prog.

    Quote Originally Posted by ArviLaanemets View Post
    On based on this (your answer to Ajax) was my post.

    1. Why have a long procedure when a single formula will do?

    2. "fnum = fnum + 1" You are adding 1 to string! It will not work in Access (but it will work in Excel)!

    3. I used CLng() - it converts string to ńumber!

    4. You probably didn't notice the main reason for using fixed-length string ID! Here will be explanation by example:

    You have a ID in string format like in your opening post with un-fixed length. Let's assume this ID is Primary Key, i.e. records in your table are ordered by it. And let's assume the order is from smallest to largest.

    You add a 1st record:
    "A-1", ...;
    You add other records up to 9th:
    "A-1", ...
    "A-2", ...
    ...
    "A-9", ...
    You add 10th record:
    "A-1", ...
    "A-10", ...
    "A-2", ...
    ...
    "A-9", ...
    You add 11th record:
    "A-1", ...
    "A-10", ...
    "A-11", ...
    "A-2", ...
    ...
    "A-9", ...
    You add 101th record:
    "A-1", ...
    "A-10", ...
    "A-101", ...
    "A-11", ...
    "A-2", ...
    ...
    "A-9", ...

    Etc.

    And the For cycle in your code to add preceding zeros is moot, as with your current code until "A-99999" you get proper ID's, but after this all will continue as in my example. I.e. You get entries like (and no leading zeros, of-course)
    "A-100001", ...
    "A-1000010", ...
    "A-10000101", ...
    "A-1000011", ...
    "A-100002", ...
    ...
    "A-100009", ...

    And in case you set the For cycle with bigger length, as I adviced, then why to use a cycle, when a simple Right() formula will do?

    Thank You Sir! GOD BLESS YOU!!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @Kundan - no need to repeat the entire post you are responding to. Just any bits that you refer to in your next post. Otherwise makes threads very long to scan through

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-09-2018, 10:30 PM
  2. ACCESS Program
    By johnseito in forum Access
    Replies: 33
    Last Post: 08-09-2014, 08:24 AM
  3. Program a 30-day trial into my Access Program?
    By genghiscomm in forum Programming
    Replies: 1
    Last Post: 05-26-2011, 02:14 PM
  4. Where is my runtime program?
    By shesgone in forum Access
    Replies: 2
    Last Post: 04-02-2010, 07:27 PM
  5. some problems with my program
    By grad2009 in forum Access
    Replies: 3
    Last Post: 02-11-2010, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums