Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page