Welcome to SPN

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

Sign Up Now!

RE: How do I get job #s in this format?

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

Tags:
  1. NetworkTrade

    NetworkTrade
    Expand Collapse
    Guest

    some other MVPs may have a more elegant solution but one way that will work
    is this:

    =DLast("jobNumber","[Test Table]")+1

    where jobNumber is the name of the table column that contains your job
    numbers i.e. 6001, 6002, etc....

    and [Test Table] is the name of the table

    (I had to use [ ] around Test Table because I put a space in the table
    name...)

    putting this equation in the Control Source of a text box on your form will
    present the next job number to the user.

    I don't think you can put this equation into the field box of the job number
    itself - as that will not work as it can't retreive the value and calculate
    the value also in the same field....

    so what you can do is put a text box with this equation and throw in a label
    that tells the user: "This is your assigned job number - put into Job Number
    field"

    a little crude but will work....probably another way to do that someone else
    will suggest
    --
    NTC


    "dogmelissa" wrote:

    > I'm trying to put together a database which is going to get really
    > complicated really quickly, but the first thing I need to do is track jobs.
    > We currently use a system with a 4 digit #, the first digit being the last
    > digit of the year (currently 6) and then a 3-digit "job #". The first job of
    > the year 2006 would be 6001 and then it goes up to there.
    > What I want to do is make this field add a new job# in the above format
    > everytime you enter a new job or quote into the database. Either I make a
    > text display of "last # used" above a number entry field, or I somehow
    > program an auto-number field on "add new record". What would be the best
    > approach to this?
    >
    > Thanks,
    > Melissa
     
  2. Loading...


  3. dogmelissa

    dogmelissa
    Expand Collapse
    Guest

    Thanks very much, that works great!

    "NetworkTrade" wrote:

    > some other MVPs may have a more elegant solution but one way that will work
    > is this:
    >
    > =DLast("jobNumber","[Test Table]")+1
    >
    > where jobNumber is the name of the table column that contains your job
    > numbers i.e. 6001, 6002, etc....
    >
    > and [Test Table] is the name of the table
    >
    > (I had to use [ ] around Test Table because I put a space in the table
    > name...)
    >
    > putting this equation in the Control Source of a text box on your form will
    > present the next job number to the user.
    >
    > I don't think you can put this equation into the field box of the job number
    > itself - as that will not work as it can't retreive the value and calculate
    > the value also in the same field....
    >
    > so what you can do is put a text box with this equation and throw in a label
    > that tells the user: "This is your assigned job number - put into Job Number
    > field"
    >
    > a little crude but will work....probably another way to do that someone else
    > will suggest
    > --
    > NTC
    >
    >
    >
     
  4. Ron2006

    Ron2006
    Expand Collapse
    Guest

    dogmelissa,

    Remember now that the number it generates for the first day in 2007
    will NOT be correct AND if someone overides that number incorrectly
    with say a leading 9 all numbers after that will have a leading 9.

    Also if you are sorting by job number and expect them to be sort of
    date sequence, the logic will fall apart on the first order in 2010.
     
  5. dogmelissa

    dogmelissa
    Expand Collapse
    Guest

    New problem: I edited Network's equation, took out the +1 part, so that it
    would just show "last number used". Now it's showing me the last number I
    used the day I put that equation in, but if I add more records, it's not
    working properply. Still showing me the # associated with that particular
    record. Help?
    My code shows this: =DLast("ProjectNumber","Projects")

    Melissa

    "Ron2006" wrote:

    > dogmelissa,
    >
    > Remember now that the number it generates for the first day in 2007
    > will NOT be correct AND if someone overides that number incorrectly
    > with say a leading 9 all numbers after that will have a leading 9.
    >
    > Also if you are sorting by job number and expect them to be sort of
    > date sequence, the logic will fall apart on the first order in 2010.
    >
    >
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

    if the project number is indeed a number then

    =dMax("[ProjectNumber]", "Projects")
     
  7. dogmelissa

    dogmelissa
    Expand Collapse
    Guest

    That's better! Thanks!

    "Ron2006" wrote:

    > if the project number is indeed a number then
    >
    > =dMax("[ProjectNumber]", "Projects")
    >
    >
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

    You're welcome...
     

Share This Page