Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Need to create an incremental sequence number

Discussion in 'Information Technology' started by kpompe, Jul 28, 2006.

  1. kpompe

    kpompe
    Expand Collapse
    Guest

    I am creating a query that needs to produce a fixed-length flat file to be
    uploaded into banking software.

    According to the file criteria, I need to create the following for each
    individual record:

    Field Size - 6 chars
    Field Type - Number
    Formatted - "000000"

    Description: This number is used as the file identifier. It should be
    unique for each transfer. For example, the first transfer would be "000001"
    and the second transfer would be "000002".

    I thought of using Autonumber - But I cannot figure out how to have the
    leading 0's. I am really stumped how to achieve this. Can anyone give some
    suggestions??

    Thanks
     
  2. Loading...


  3. RuralGuy

    RuralGuy
    Expand Collapse
    Guest

    On Thu, 29 Jun 2006 12:53:02 -0700, kpompe <kpompe@discussions.microsoft.com>
    wrote:

    >I am creating a query that needs to produce a fixed-length flat file to be
    >uploaded into banking software.
    >
    >According to the file criteria, I need to create the following for each
    >individual record:
    >
    >Field Size - 6 chars
    >Field Type - Number
    >Formatted - "000000"
    >
    >Description: This number is used as the file identifier. It should be
    >unique for each transfer. For example, the first transfer would be "000001"
    >and the second transfer would be "000002".
    >
    >I thought of using Autonumber - But I cannot figure out how to have the
    >leading 0's. I am really stumped how to achieve this. Can anyone give some
    >suggestions??
    >
    >Thanks


    I would keep the number in a system file and increment it every time I used it.
    Format([YourNumber],"000000") will give you a 6 digit number.
    _______________________________________________
    hth - RuralGuy (RG for short)
    Please post to the NewsGroup so all may benefit.
     
  4. Ron2006

    Ron2006
    Expand Collapse
    Guest

    DONT use autonumber.


    If you ever had to rerun the process, you will have a problem because
    the autonumber will have been incremented. If it is a field on a
    special table you can always reset it at least manually to get it to
    have the proper sequential number.

    Ron
     
  5. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    This is an article about creating custom counters:

    http://support.microsoft.com/kb/q191253/

    There is also a version for dao 3.6/ jet 5, but
    it is substantially the same, and i don't have
    the reference.

    It's more sophisticated than you want, but you might
    be interested anyway.

    (david)

    "kpompe" <kpompe@discussions.microsoft.com> wrote in message
    news:5A9AC628-4A1E-457B-8D2B-96ECD0B6B2CF@microsoft.com...
    >I am creating a query that needs to produce a fixed-length flat file to be
    > uploaded into banking software.
    >
    > According to the file criteria, I need to create the following for each
    > individual record:
    >
    > Field Size - 6 chars
    > Field Type - Number
    > Formatted - "000000"
    >
    > Description: This number is used as the file identifier. It should be
    > unique for each transfer. For example, the first transfer would be
    > "000001"
    > and the second transfer would be "000002".
    >
    > I thought of using Autonumber - But I cannot figure out how to have the
    > leading 0's. I am really stumped how to achieve this. Can anyone give
    > some
    > suggestions??
    >
    > Thanks
     

Share This Page