How do I get job #s in this format?

[Guest]

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

 

[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

 

[Guest]

Thanks very much, that works great!

 

[Ron2006]

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.

 

[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]

if the project number is indeed a number then

=dMax("[ProjectNumber]", "Projects")

 

[Guest]

That's better! Thanks!

 

[Ron2006]

You're welcome...