Auto numbbers

[d9pierce]

Hi all,
I am trying to create an auto increment number that will be called job
#.

Its format needs to be this 07-1-0604 Meaning the 07 is the year, the
1 would count the number of projects done in relation to the project
name and address, the 06 would be the project manager ID and the
01,02,03,04...would be the auto increment count of each project that
the project manager has.

Any suggestions or examples. I cant find any really good examples of
how to do this.
Thanks,
Dave

 

[Guest]

Dave,

I have a great example for this on a database I wrote. Unfortunately, my
solution so *clever* (read: lengthy). I will have to take a look at it to
remember all the details. I will gladly send you my example and you can fit
to your need; but, the db is at work. I will send tomorrow a.m.

There is one thing her you want to be careful about. Do NOT use this as
your Primary Key!

Xraywhiskey

 

[d9pierce]

Dave,

I have a great example for this on a database I wrote. Unfortunately, my
solution so *clever* (read: lengthy). I will have to take a look at it to
remember all the details. I will gladly send you my example and you can fit
to your need; but, the db is at work. I will send tomorrow a.m.

There is one thing her you want to be careful about. Do NOT use this as
your Primary Key!

Xraywhiskey







- Show quoted text -

Thanks so much, I would love to see it! If easier you can send
directly to (e-mail address removed)

 

[Guest]

You can write a function that will first look up the number of projects by
the desired manager, and then append that value to the current date in the
desired format, all as a string. If you are doing data entry through a form,
you simply call this function at the BeforeInsert event. Once you have the
string, assign it to the appropriate field. If you want to use this as a
Primary Key, you will probably want to add some other unique identifier(s) to
ensure that you don't run into Key conflicts.

 

[rwr]

Hi all,
I am trying to create an auto increment number that will be called job
#.

Its format needs to be this 07-1-0604 Meaning the 07 is the year, the
1 would count the number of projects done in relation to the project
name and address, the 06 would be the project manager ID and the
01,02,03,04...would be the auto increment count of each project that
the project manager has.

Any suggestions or examples. I cant find any really good examples of
how to do this.
Thanks,
Dave

Your going to have to do some processing to create this type of number.
Create a function that you call like this.

NewNumNeeded=FCreateKey()

In this routine I would convert any numbers to text as you create the
key so that all of the digits end up in the correct position.

Get your year as 2 digits.

lookup the project manager record and get the key, then count how many
project competed by this manager. Concatenate that to the year

dim strNewNum as string
dim newNum as long
strnewNum = Format(somedatfield,"yy")

strNewNum=strNewNum & format(ProjectKmanagerkey,"00"

Then you can do a dmax to find the value of the number of projects
completed, add 1 to that value, and then Concatenate that to you strnewNum.

Comvert the final text string to a long data type.

newNum = Clng(strNewNum)

to return the number before you leave the function.

FCreateKey=newNum

So it would look something like this.

public Function FCreateKey(byVal ProjectManager as string, byVal _
ProjectAddress as string) as long

dim strNewNum as string
dim newNum as long
strnewNum = Format(somedatefield,"yy")

strNewNum=strNewNum & format(ProjectKmanagerkey,"00")

newNum = Clng(strNewNum)

FCreateKey=newNum

This is air code just to give you an idea of what is needed.

Ron