Unique number based on year & record number

[KerryL]

Well, I created a unique Notice Number using the Now function in a date field formatted as yymmddhhmmss. This produced Notice Numbers like 061215115742 or 061218081236. However, mgmt thinks the number is too long. They want to go back to the numbering scheme used when the program was written for Dataflex: yyyynnn, where yyyy = current year and nnnn = record number. (They enter 400 records/year max.)

So I'm looking for some help to code this in a way where no maintenance will be required. IOW, when the new year rolls around the Notice Number would automatically reset itself. For example, on 1-1-07 it would reset from, say, 2006335 to 2007001.

I think I can manage the yyyy part, but I'm not sure how to produce the nnn and also get it to reset for a new year.

Is there a VBA expert who can help me write code that uses the current year & record count to produce the Notice Number, and also automatically resets itself in a new year?

Thanks in advance,
Kerry

 

[IGPCS]

HI Kerry how r u?

i didnt realy understand what you need, do u need to make a unique field thats combined from year and an aditional 4 digit runing number (something like we spoke a week ago)?

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

Hi IGPCS,
I am fine, thanks. You?

Yes, I'm sorry...this is the same issue we talked about last week. I decided to simplify things by using the Now function and a formatted date field, but mgmt doesn't like the 12-digit number. They prefer the yyyynnn format.

The code you helped me with worked except that it kept creating numbers like 200612 instead of 2006012. Plus, while it was counting records it seems as if it was looping through the table a lot, and meanwhile the form was frozen.

Perhaps if we try it again we can get it figured out?

Kerry

 

[SkipVought]

Hi,

SeqNbr = Format(Date, "yyyy") & Format(TheNumber, "000")

Skip,

_[red][/red]

 

[IGPCS]

so why dont you try the code and do you remeber i gave you an if staement that you told me you will use it with the Select Case it should count the variables lenght and it should add you the 0 befor the count and can you please post you code so i will try to fix it up so the form should freeze but please paste the whole code of the event

Thanks

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[PaulBricker]

The easiest way might be to create a query that counts the records in the current year. Something like

SELECT Count(Query1.myField) AS CountOfmyField
FROM Query1
WHERE (((Year([myField]))=Year(Date())));

Then on your form, you could have a control (call it Ctl1) that looks up the Count value in this query using DLookup()

Then when a new record is created, your number gets created using an expression like

=Format(Date(),"yyyy") & Format((Format([Ctl1],"000")+1,"000")


In the query, you might have to pull the year out of your unique number using the Left function to compare with the current year, but it shouldn't be too hard.

Paul

 

[barny2006]

you can also use this:
your_number = 12
new_number = right("0000" & your_number,4)
this will always give you a 4 digit number with leading zeros.

 

[IGPCS]

well format is the best function for this

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY