Need help with Auto entry Serial Number

[Dragon Lady]

Hello everyone - I am new to this forum, and I hope someone can help me

I am building a mail order data base for my company.

We need a unique invoice sequence# comprised of the following

If Month=3 Year=06 Number= auto entry=0000+1

The return of the invoice number needs to look like this: 03060001

They are also asking that at each month's end. the first 2 characters roll over to the next month.

I have spent days trying to find a solution for this. Any suggestions warmly welcomed!

Edited March 15, 2006 by Guest

[Genx]

... but the first 2 characters are the month...Anyway, i'm thinking that you go with a calculation field that combines a serial field and an unstored calculation resulting in text.

I.e. Month(Get(CurrentDate)) & Right(year(Get(CurrentDate));2) & SerialField

Hmmm, you could do it with a self join relationship, where the criteria is an unstored calc field on parent table resulting in a date, the calc itself being Month(Get(CurrentDate)) & Year(Get(CurrentDate)) and resulting in number.

On the child end of the relationship you have an auto enter number = Month(Date) & Year(Date), where date is an auto enter date field that enters on creation of the invoice record. Now finally, to trigger your reset portion of the script, you would use the following check:

If [Count(SomeField) = 0]

Perform Script [ResetScript]

End If

*SomeField is a field in your child table that will always have some content in it...*

Hope this helps...and i haven't confused you, and those no flaw in my logic, and someone else hasnt answered this already... Lol, see my disclaimer.

~Genx

[Genx]

Oh yeh, welcome to the forums :)

[Dragon Lady]

Hi! thanks for your solution:

Month(Get(CurrentDate)) & Right(Year(Get(CurrentDate));2)& newSerial# Invoice

did return what I needed: 3060001

However, any ideas on how I can get the zero to appear on single digit months? to return: 03060001

Edited March 15, 2006 by Guest

[Raybaudi]

Right("0" & Month(Get(CurrentDate));2)& Right(Year(Get(CurrentDate));2)& newSerial# Invoice

[Lee Smith]

Hi Kathy, and Welcome to the Forum.

Beware, comment will probably streamline this.

I would use Two fields

[color:blue]Serial ([color:red] Auto Enter - Serial, [color:green]Number) Start with [color:blue]0001 increment by [color:blue]1

[color:blue]c_Serial ([color:red]calc, [color:green]Text Result) =

Right("00" & Month(Get(CurrentDate)); 2) & Right(Year(Get(CurrentDate));2) & Serial

HTH

Lee

[Lee Smith]

Hi Daniele,

I took too long prettying mine up

Lee

[comment]

Beware, comment will probably streamline this.

No, but I don't think it's a good idea to auto enter "0001" into a NUMBER field.

I'd leave SerialID as a number, auto-entering from 1. Then:

Right ( "00" & Month ( Get(CurrentDate) ) ; 2 )

&

Right ( Year ( Get(CurrentDate) ) ; 2 )

&

Right ( "000" & SerialID ; 3 )

[Lee Smith]

Hocked him .... LOL

Actually, I had started out using your Serial Calculation, then switch it to see if the Number would hold, when it did, i just used it instead of the [color:blue]Right ( "000" & SerialID ; 3 )

[comment]

Yes, 7 and 8 are quite forgiving about leading and trailing zeros in a number field. Still, it's not good practice.

[Raybaudi]

Hi comment and Lee

both of you have used "00" but that calc needs only one "0"

[Lee Smith]

I saw your calculation when i posted. And I realized that a second zero was unnecessary, however, it dose make the calculation easier to read.

[comment]

Well I could say you need "00" in case the rest is empty... But the real reason is similar to what Lee said: it helps me to understand what that part does.

[Genx]

Lol, streamlining, i love you guys

~Genx

[Dragon Lady]

Your were both kind to offer solutions.

Problem is:

( Get(CurrentDate) )

only allows the "current date" - I need to use a different date, like sales date, which is based on the creation date.

Any other ideas?

[Lee Smith]

Just Replace the occurrences of [color:blue]Get(CurrentDate) with [color:blue]Your Date Field of Choice and it will still work.

However, if you are using a date that isn't the Current Date, then the Auto Enter - Serial, could be something different than in date order.

as an example, lets say you created three invoices today, and the order is 1, 2, 3. Lets say that the Sales dates involved were 3/1/2006. 3/3/2006 and 2/24/2006. Your numbers would be

0306001

0306002

0206003

HTH

Lee

[LaRetta]

Hi Kathy

I can't help but wonder ... and maybe this is no longer an issue but:

In the olden days, a computer's date/time could stop working. It seemed to be because of the CPU battery? I'm no hardware person and I haven't heard of that happening for 6-7 years now. But basing something as critical as IDs on a date strikes me as potentially dangerous. If this serial is the basis for your relationships, someday it might bite you. I don't know whether you are served or not, but I believe creation dates pull from the workstation. What if a User changes their computer date? OR what if this solution is moved overseas and begins to use International settings? I believe in this instance it probably wouldn't matter but I would certainly test it). And the reason the business wants this number is to make sense of it. If they move their base office to Great Britian then they will be unhappy with it there. Will all of your attempts to structure it (and the date) hold? Particularly when using text dates?

If you are served, you might use Get(CurrentHostTimeStamp) instead to at least cut down on the possibility of error. I just wanted to express my concern. Again, I may be overly fussy - I've been accused of that before.

LaRetta