Date/Time format 4

[newguy86]

I have a date/time stamp that is generated using the following format:

"yymmdd-hhnnss"

As I understand it, this format should produce a value that looks similar to this:

091127-074523

The issue that I am having is the value transmits differently depending on where I display it when I use this same format.

For example, if I look at the same value in the table and in an auto-generated email this is what I see:
-Table => 091127-74523
-Email => 091127-074523

This is causing me some issues because if someone takes the value from the email and tries to search for it in the table then they will not find that record without removing the zero immediately after the dash.

If anyone can provide any assistance, it would be greatly appreciated.

Also, almost forgot to mention that I am using Access 2003.

Thanks,


Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[newguy86]

Sorry, meant "effect" instead of "affect".

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[JoeAtWork]

on a scale larger than what the AutoNumber feature could handle

I don't know how many databases you've dealt with but I've probably had a few hundred over time. I've yet to see a table that even aproaches the limit of an AutoNumber (which being a Long data type is somewhat over 2 billion).

Lets use some math to determine if your solution really is on "a scale larger than AutoNumber could handle". Let's assume a record is created every second (any higher than that and your solution will have the duplicate IDs error - something that would not happen if you used autonumber).

How many records can be created in a day?
60 seconds X 60 minutes X 24 hours = 86,400

How many records in a year?
86,400 X 365 = 31,536,000

So, using the same assumption of 1 record created every second, let's calculate when you would get an overflow error from using AutoNumber:

2,147,483,647 / 31,536,000 = 68.0962

So, if I wrote a program using autonumber and you wrote yours using your solution, and we ran them non-stop at one record per second, somewhere in the 68th year my program would get an overflow error. And at that point your great-grandchildren could tell my great-grandchildren how silly I was for not designing on a "larger scale".

Getting back to reality, my point here is that in order to avoid the (virtually impossible) problem of running out of AutoNumbers, you've created a solution that will almost certainly give you duplicate ID errors - the only question is how often it will happen.

 

[newguy86]

JoeAtWork,
You present a very compelling argument. One with which I would have to agree with you on. You are correct, it would take an ungodly long amount of time before I run out of actual numbers to use if I go down the Autonumber path. (Although, I do find the scenario amusing of me telling my great-grandchildren to tell your great-grandchildren to tell you "I told you so!"). Unfortunately, that was not the issue that I was refering to (I probably should have used a better choice of words when I originally stated that).

The issue I was referring to was one that a co-worker of mine had. She was maintaining an old logging database for her department and every few days the database would lock up and she would have to have all users close the database entirely so that everything could completely reset. She couldn't figure out what was causing the issue except that it would only happen under certain conditions. Some of those conditions were, 1.) more than five (5) people would have to be in the process of logging a new record at the same time, and 2.) one of those people would have to have a new record sitting open that was locked and unsaved for an undetermined amount of time. She eventually resolved the issue by changing the autonumber field to a random number field.

She never figured out what was causing the issue, but she did mention that it could have been just about anything from poor database design to some setting that was changed.

Also, I do understand that with this solution there is still a chance of receiving duplicate id errors. But with the small number of users, the chances of that happening will be quite rare.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[MajP]

faq700-184