Results 1 to 5 of 5
  1. #1
    grkchakri is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2018
    Posts
    1

    Increment numbers

    Hi All

    How do i increment numbers according to the style i wanted



    Eaxmple : EMP ID: EMP001 (increment number by 2 ) say EMP003, EMP005

    Any suggestions how do i do that. just kick started learning access

    Thank you all

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Before you go too far to increment a number by 2 and concatenate that number to a string, I suggest you spend time doing some research on Database Concepts --especially Normalization. Here is a link with lots of info in various formats.
    See the Normalization link and the DBMS principles by Don (atomic values).
    Also, if you look at the bottom of the page when reading a post, you'll see a list of links under the heading Similar Threads which can be a great starting point.

    Google and youtube can also be helpful tools.

    Good luck.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Generating custom unique identifier is a common topic. Suggest you search those keywords for more threads. Here is one https://www.accessforums.net/showthread.php?t=23329
    Last edited by June7; 07-22-2018 at 10:38 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In addition to the above, life would be easier if you would forego the "EMP" part. It can easily be inserted when required, such as when a form loads. Numbers alone are the easiest to handle; text almost as much. Together in one field, not so much.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    In case the ID is for having unique identifier only, an autonumeric ID is best choice - you don't have to bother with maintaining of ID's at all.

    You need a custom ID, when the ID carries some additional information outside of your application, and you prefer not to have duplicate unique indexes in table.

    I myself use such ID in my IT Devices database - in format "CCyyyymmddnnn", where
    "CC" is device group identifier (1st character determines main group, 2nd one subgroup - e.g "CC" for thin client, "CD" for desktop, "PM" for multifunctional printer etc.);
    "yyyymmdd" is registering date for device;
    "nnn" is incremental number for devices (with preceeding zeros) of this group registered on this date. I.e. at given date up to 999 devices of same group may be registered (so long the max for table is somewhat "070"+)

    The reason for using such ID is, that it is printed on sticker on every device, and any IT-guy can see at glance, what type device it is, and how old it is, without having to consult the database (e.g. when collecting devices to be returned to leasing firm). Also there may be cases, when devices are registered afterwards, and autonumeric ID would order devices in wrong order.

    In my application, the device registry form is subform of unbound main form, and this subform is linked with control to select device group (i.e. user operates devices from certain group only). Whenever a new device is registered, all controls on form are disabled except a control for registering date (with current date as default value) and a button for registering date is displayed. When the button is clicked, the procedure reads group and date strings, uses DLookup() to find max value of last 3 characters of all ID's with same group and date strings, and ads 1 to result. After that composites the string for new ID, writes it into control for ID, and enables other controls again. Any other action except clicking the button aborts creating new record. After that user can fill rest of fields. The record is saved whenever user moves to next record, selects Save from menu, etc. (required fields are checked of-course). As the new ID is written into control only, the user has always option to press Esc twice and abandon the new record creating.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-08-2016, 03:04 PM
  2. Replies: 13
    Last Post: 05-01-2013, 09:01 AM
  3. Replies: 1
    Last Post: 02-23-2012, 11:48 AM
  4. Renumbering Auto-Increment numbers
    By svcghost in forum Database Design
    Replies: 8
    Last Post: 02-19-2011, 08:48 AM
  5. Replies: 5
    Last Post: 11-12-2010, 12:10 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