Auto-Generating a Number w/ Parsed Date

[JoeTekTips]

Hello,
I have to produce an automatically generated number using VBA for an Access db. The format is supposed to be the year followed by the day and month followed by an incremental number. For example;
200505010001
200505010002
200505010002 etc....
The incremental number part would reset from day to day. I'm not sure how to properly parse out the date or the best way to handle the incremental number. I was thinking about making a table with a date and an autonumber for the primary key to get the values.
If anyone can help on this one, I would really appreciate it!
-Joe

 

[TonyJollans]

Hi Joe,

If you're generating these numbers to be keys in a table, there's no need to store anything separately as you already have the information you need. Normal cautions apply in a multi-user environment but to find the highest used number for any particular date use a SELECT MAX and add 1 to the result:

[blue][tt]SELECT Nz(Max(YourNumber),Format(Date() ,"yyyymmdd000")) AS MaxNumber
FROM YourTable
WHERE YourNumber > Format(Date() ,"yyyymmdd000")
AND YourNumber < Format(Date()+1,"yyyymmdd000")[/tt][/blue]

Then MaxNumber + 1 is what you want

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JoeTekTips]

Thanks Tony, you got me off to a start.
One thing that I realized is that I don't need an auto-number. I just need a number in a table that can be manually incremented and reset to zero from day to day.
With that said, I tried you suggestion. He is what it looks like;

SELECT Nz(Max([tblGetValue].[GetNumber]),Format(Date(),"yyyymmdd""000""")) AS MaxNumber
FROM tblGetValue
WHERE (((tblGetValue.GetNumber)>Format(Date(),"yyyymmdd""000""") And (tblGetValue.GetNumber)<Format(Date()+1,"yyyymmdd""000""")));

Here, tblGetValue is simply a table with one date element; a non-primary key number. The result of the query is this;

20050502000

This gives me the date in the proper format, which is half the problem. What I still need is an incremented number concatenated to the end of it.

Now, I should have mentioned a little more background on this. I have an input form that will be used for entering a new job ticket. The new job ticket should have a new ticket number, in the format mentioned above. The ticket number is the primary key of the data source for the form. The text field for the ticket number can be set to not visible, and the user can be notified of the new number after the form is completed and entered.
The idea is that some action triggers the VBA code that generates the new number and assigns it the non-visible text box (or maybe just the data value, TicketNum). I was thinking when the first tab is left, the VBA code would run from the “lostFocus” attribute of the first text box.

With all of that said, and with seeing the formatting technique that you provided, maybe the best way is to not use tables at all and create a variable and assign it first;
DateVal = Format(Date(),"yyyymmdd""000""")
and then the concatenated incremented number value.
Perhaps, instead of taking the value from a table, I should use a Global Variable. I know about Global variables, but have not worked with them before. This db will be split, so I do not know if that will matter. If a global variable is used, than the pseudo code could be something like;
GlobalNum = GlobalNum + 1
NewTicketNum = DateVal + GlobalNum

Ideally, the incremented number would be reset to 1 from day to day.

Do you think that would work?
Thanks a bunch,
-Joe

 

[JoeTekTips]

I got this to work on the lost focus action;


Private Sub Combo55_LostFocus()
JobTicketNumber = Format(Date, "yyyymmdd""000""") & "1"
End Sub

The result is:

20050502001

But, of course, I need to make this a global value that I can increment.
I tried to put a variable in the Class Module (my first time working with Class Module), but I keep gettiing an "Invalid Outside Porcedure" error message. I even get this when I am not using the variable!
Please Help!!!!!!

-Joe

 

[TonyJollans]

Hi Joe,

Sorry, been busy most of the day. I don't think you have fully understood what I posted. There is no need to have a separate table or a variable or anything else - all the information you need is already in your Job Ticket Table.

For simplicity's sake let's make some assumptions:

Your Table is called [blue]JobTable[/blue]
It has a key field called [blue]JobTicket[/blue]
And one other field called [blue]JobInfo[/blue]

You have a Form with an input textbox for the JobInfo field, and a button called [blue]btnUpdate[/blue]

Put this code in the Form

[blue]Private Sub btnClick_Click()
MaxNumber = DLookup("Nz(Max([JobTicket]),Format(Date(),""yyyymmdd000""))", "JobTable", _
                    "JobTicket>Format(Date(),""yyyymmdd000"") And JobTicket<Format(Date()+1,""yyyymmdd000"")")
SQL = "Insert into JobTable (JobTicket, JobInfo) values(" & MaxNumber + 1 & ",Me.JobInfo);"
CurrentDb.Execute SQL
End Sub[/blue]

Now I know this needs refining (and should use ADO instead of DAO, etc) but I just typed it in quickly in Access to try to avoid silly mistakes. You will of course have other fields and do validation, etc, etc. The point is it will always find the highest number used for any date you give it (returning yyyymmdd000 the first time it's used each day) and you can just add 1 to that to create a new record and next time it will find the new number you've used and you can add 1 to that etc. If you are in a multi-user environment you need to handle concurrent updates but you can worry about that when you have the basic structure working.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JoeTekTips]

Bravo!

I used this in the LostFocus property of the firt in tab line of text boxes (actually a combo box);

MaxNumber = DLookup("Nz(Max([JobTicketNumber]),Format(Date(),""yyyymmdd000""))", "tblJobTicket", _
"JobTicketNumber>Format(Date(),""yyyymmdd000"") And JobTicketNumber<Format(Date()+1,""yyyymmdd000"")")

JobTicketNumber = MaxNumber

This gives me a JobTicketNumber right away in my Primary Key. This way, I don't even have to worry about the insert to the table. The good thing is that that, somehow, this only happens the first time leaving the combo box.

Thanks a million!!!

 

[JoeTekTips]

This is a follow up.
The code above does work wonderfully.
Now, I've been given an additional requirement;

I now need the value eliminate the first three places. For example, instead it beginning 2005.... it should start 5.....

Anything that I have trying so for has been unsucessful.

Any idea?

Thanks,
Joe

 

[TonyJollans]

Hi Joe,

I guess the simplest way (as you always know the length) is to take the rightmost 8 digits which will give you a atring which you can then convert back to a number ...

[blue][tt] MaxNumber = CLng(Right([/tt][/blue][tt]DLookup( .... )[/tt][blue][tt],8))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JoeTekTips]

Tony,
Thanks for your quick reply. I tried this, which formats it exactly the right way, but does not increment each time like before.
Any ideas?
Thanks again,
-Joe

 

[earthandfire]

Try


Function SlimDate(Optional AddIndex As Long = 0) As Long

SlimDate = Right(Format(Date, "yyyymmdd000"), 8) + AddIndex

End Function


and then replace both occurences of ...

Format(Date(),""yyyymmdd000"") with SlimDate()

and

Format(Date()+1,""yyyymmdd000"") with SlimDate(1)

Hope this helps.

 

[earthandfire]

Note the function will need to be placed OUTSIDE of the LostFocus Event handler, preferably near the top of the Form's module or in a separate global module.

 

[TonyJollans]

Hi Joe,

You're absolutely right - I hadn't immediately realised the implications. It should work better if you use the [blue]Clng(Right, ... 8))[/blue] construct on the individual dates instead ..

[blue]MaxNumber = DLookup("Nz(Max([JobTicketNumber]),clng(right(Format(Date(),""yyyymmdd000""),8)))", _
                    "tblJobTicket", _
                    "JobTicketNumber>clng(right(Format(Date(),""yyyymmdd000""),8))" & _
                    "And JobTicketNumber<clng(right(Format(Date()+1,""yyyymmdd000""),8))")
[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JoeTekTips]

Works great now.

You the man, Tony!

-Joe

 

[JoeTekTips]

This has been working really well, but recently the database has been split. Now, if each user starts a record at the same time, the get the same ticket number. This happens until the record is saved and closed.

Does anyone have an idea on how to avoid this?

Thanks,
Joe