Welcome to SPN

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

Sign Up Now!

Automatically create a UNIQUE # (autonumbering w/characters)

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

  1. Crystal

    Crystal
    Expand Collapse
    Guest

    Hi Everyone!

    I have a table and I need to input an Item No. for each Part No. given.

    *Each Item No. has to be unique (sort of like autonumber, primary key)
    *Each Item No. begins with CRI
    *Each Item No. ends with 000

    Here is where the tricky part (at least for me) comes in. There needs to be
    4 characters between the CRI and the 000...example CRIAERD000...NO SYMBOLS
    THOUGH. It can be any 4 characters, it just has to be unique.

    Is there a way I can have Access create those 4 characters for me? Please
    help!!!

    Thanks :)
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    Crystal

    Any reason you couldn't use an autonumber for the part in the middle?
    They're unique (but digits, not alphabetic characters).

    If you need alphabetic characters, could you generate a random number for
    each charcter, then use the CHR() function to return an alphabetic character
    for the random number? You'd have to limit the range of random numbers to
    those for which CHR() generates alpha characters.

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP


    "Crystal" <Crystal@discussions.microsoft.com> wrote in message
    news:6299D7D1-EF29-4388-96B1-3A639B48AC23@microsoft.com...
    > Hi Everyone!
    >
    > I have a table and I need to input an Item No. for each Part No. given.
    >
    > *Each Item No. has to be unique (sort of like autonumber, primary key)
    > *Each Item No. begins with CRI
    > *Each Item No. ends with 000
    >
    > Here is where the tricky part (at least for me) comes in. There needs to
    > be
    > 4 characters between the CRI and the 000...example CRIAERD000...NO SYMBOLS
    > THOUGH. It can be any 4 characters, it just has to be unique.
    >
    > Is there a way I can have Access create those 4 characters for me? Please
    > help!!!
    >
    > Thanks :)
     
  4. Crystal

    Crystal
    Expand Collapse
    Guest

    Hi Jeff -

    Maybe I should have posted my question in the "New Users" section...LOL

    I don't see why I couldn't just use numbers, it would be easier, however we
    currently have Item #'s setup with alpha & numeric.

    As far as your suggestion below...sounds reasonable...but I'm still pretty
    new and you've stumped me on the CHR( ). Could you show me what the code
    would look like?

    Thanks!

    "Jeff Boyce" wrote:

    > Crystal
    >
    > Any reason you couldn't use an autonumber for the part in the middle?
    > They're unique (but digits, not alphabetic characters).
    >
    > If you need alphabetic characters, could you generate a random number for
    > each charcter, then use the CHR() function to return an alphabetic character
    > for the random number? You'd have to limit the range of random numbers to
    > those for which CHR() generates alpha characters.
    >
    > Regards
    >
    > Jeff Boyce
    > Microsoft Office/Access MVP
    >
    >
    > "Crystal" <Crystal@discussions.microsoft.com> wrote in message
    > news:6299D7D1-EF29-4388-96B1-3A639B48AC23@microsoft.com...
    > > Hi Everyone!
    > >
    > > I have a table and I need to input an Item No. for each Part No. given.
    > >
    > > *Each Item No. has to be unique (sort of like autonumber, primary key)
    > > *Each Item No. begins with CRI
    > > *Each Item No. ends with 000
    > >
    > > Here is where the tricky part (at least for me) comes in. There needs to
    > > be
    > > 4 characters between the CRI and the 000...example CRIAERD000...NO SYMBOLS
    > > THOUGH. It can be any 4 characters, it just has to be unique.
    > >
    > > Is there a way I can have Access create those 4 characters for me? Please
    > > help!!!
    > >
    > > Thanks :)

    >
    >
    >
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    Crystal

    In a query (or in a procedure you'd write), you'd use the RND() function and
    the CHR() function to 1) generate a random number; 2) get an ASCII
    character based on that random number.

    Access HELP and VBA HELP can give you both the correct syntax and examples
    for each.

    The one thing you'd need to do is make sure that the range of RND() numbers
    falls in the range of numbers that will produce usable characters. For
    example, if your RND() returns "10", the CHR() function will return a
    carriage return (or is it a line-feed, I can never remember which is which).
    This is NOT an alphanumeric character that you'd be able to use.

    And now that I think about it, since you'd be using a random number
    function, there's no guarantee that you wouldn't end up with a duplicate!
    Maybe you better reconsider using the Autonumber for this portion...

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP

    "Crystal" <Crystal@discussions.microsoft.com> wrote in message
    news:72E0895C-3CB9-4F54-A671-E929F5186B78@microsoft.com...
    > Hi Jeff -
    >
    > Maybe I should have posted my question in the "New Users" section...LOL
    >
    > I don't see why I couldn't just use numbers, it would be easier, however
    > we
    > currently have Item #'s setup with alpha & numeric.
    >
    > As far as your suggestion below...sounds reasonable...but I'm still pretty
    > new and you've stumped me on the CHR( ). Could you show me what the code
    > would look like?
    >
    > Thanks!
    >
    > "Jeff Boyce" wrote:
    >
    >> Crystal
    >>
    >> Any reason you couldn't use an autonumber for the part in the middle?
    >> They're unique (but digits, not alphabetic characters).
    >>
    >> If you need alphabetic characters, could you generate a random number for
    >> each charcter, then use the CHR() function to return an alphabetic
    >> character
    >> for the random number? You'd have to limit the range of random numbers
    >> to
    >> those for which CHR() generates alpha characters.
    >>
    >> Regards
    >>
    >> Jeff Boyce
    >> Microsoft Office/Access MVP
    >>
    >>
    >> "Crystal" <Crystal@discussions.microsoft.com> wrote in message
    >> news:6299D7D1-EF29-4388-96B1-3A639B48AC23@microsoft.com...
    >> > Hi Everyone!
    >> >
    >> > I have a table and I need to input an Item No. for each Part No. given.
    >> >
    >> > *Each Item No. has to be unique (sort of like autonumber, primary
    >> > key)
    >> > *Each Item No. begins with CRI
    >> > *Each Item No. ends with 000
    >> >
    >> > Here is where the tricky part (at least for me) comes in. There needs
    >> > to
    >> > be
    >> > 4 characters between the CRI and the 000...example CRIAERD000...NO
    >> > SYMBOLS
    >> > THOUGH. It can be any 4 characters, it just has to be unique.
    >> >
    >> > Is there a way I can have Access create those 4 characters for me?
    >> > Please
    >> > help!!!
    >> >
    >> > Thanks :)

    >>
    >>
    >>
     
  6. Crystal

    Crystal
    Expand Collapse
    Guest

    THANKS JEFF
    : )

    "Jeff Boyce" wrote:

    > Crystal
    >
    > In a query (or in a procedure you'd write), you'd use the RND() function and
    > the CHR() function to 1) generate a random number; 2) get an ASCII
    > character based on that random number.
    >
    > Access HELP and VBA HELP can give you both the correct syntax and examples
    > for each.
    >
    > The one thing you'd need to do is make sure that the range of RND() numbers
    > falls in the range of numbers that will produce usable characters. For
    > example, if your RND() returns "10", the CHR() function will return a
    > carriage return (or is it a line-feed, I can never remember which is which).
    > This is NOT an alphanumeric character that you'd be able to use.
    >
    > And now that I think about it, since you'd be using a random number
    > function, there's no guarantee that you wouldn't end up with a duplicate!
    > Maybe you better reconsider using the Autonumber for this portion...
    >
    > Regards
    >
    > Jeff Boyce
    > Microsoft Office/Access MVP
    >
    > "Crystal" <Crystal@discussions.microsoft.com> wrote in message
    > news:72E0895C-3CB9-4F54-A671-E929F5186B78@microsoft.com...
    > > Hi Jeff -
    > >
    > > Maybe I should have posted my question in the "New Users" section...LOL
    > >
    > > I don't see why I couldn't just use numbers, it would be easier, however
    > > we
    > > currently have Item #'s setup with alpha & numeric.
    > >
    > > As far as your suggestion below...sounds reasonable...but I'm still pretty
    > > new and you've stumped me on the CHR( ). Could you show me what the code
    > > would look like?
    > >
    > > Thanks!
    > >
    > > "Jeff Boyce" wrote:
    > >
    > >> Crystal
    > >>
    > >> Any reason you couldn't use an autonumber for the part in the middle?
    > >> They're unique (but digits, not alphabetic characters).
    > >>
    > >> If you need alphabetic characters, could you generate a random number for
    > >> each charcter, then use the CHR() function to return an alphabetic
    > >> character
    > >> for the random number? You'd have to limit the range of random numbers
    > >> to
    > >> those for which CHR() generates alpha characters.
    > >>
    > >> Regards
    > >>
    > >> Jeff Boyce
    > >> Microsoft Office/Access MVP
    > >>
    > >>
    > >> "Crystal" <Crystal@discussions.microsoft.com> wrote in message
    > >> news:6299D7D1-EF29-4388-96B1-3A639B48AC23@microsoft.com...
    > >> > Hi Everyone!
    > >> >
    > >> > I have a table and I need to input an Item No. for each Part No. given.
    > >> >
    > >> > *Each Item No. has to be unique (sort of like autonumber, primary
    > >> > key)
    > >> > *Each Item No. begins with CRI
    > >> > *Each Item No. ends with 000
    > >> >
    > >> > Here is where the tricky part (at least for me) comes in. There needs
    > >> > to
    > >> > be
    > >> > 4 characters between the CRI and the 000...example CRIAERD000...NO
    > >> > SYMBOLS
    > >> > THOUGH. It can be any 4 characters, it just has to be unique.
    > >> >
    > >> > Is there a way I can have Access create those 4 characters for me?
    > >> > Please
    > >> > help!!!
    > >> >
    > >> > Thanks :)
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page